Geek asks and answers

SQL server cannot create table in database due to permissions

I am using SQL Server 2008 Express R2. I tried to check a couple of requests when I started getting this error:

  Msg 2760, Level 16, State 1, Line 2
    The specified schema name "t_one" either does not exist or you do not have permission to use it.

SQL:

  CREATE TABLE t_one.clients
(
t_id int NOT NULL PRIMARY KEY IDENTITY,
colOne varchar(255) NOT NULL,
colTwo varchar(255) NOT NULL,
colThree varchar(255) NOT NULL,
colFour varchar(255) NOT NULL,
CONSTRAINT pk_testID PRIMARY KEY(t_id)

)

I granted permissions for my user profile, just using the interface, and after I clicked OK / save, he did not apply them - when I returned to the permissions for my user, they were all disabled again.

+3
source share
3 answers

Try running it like this:

CREATE SCHEMA t_one
CREATE TABLE t_one.clients
(
t_id int NOT NULL PRIMARY KEY IDENTITY,
colOne varchar(255) NOT NULL,
colTwo varchar(255) NOT NULL,
colThree varchar(255) NOT NULL,
colFour varchar(255) NOT NULL,
CONSTRAINT pk_testID PRIMARY KEY(t_id)

)
+19
source

To check and create, if the circuit does not exist, you can have the following work in a separate batch

IF NOT EXISTS ( SELECT  *
                FROM    sys.schemas
                WHERE   name = N't_one' ) 
    EXEC('CREATE SCHEMA [t_one] AUTHORIZATION [dbo]');
GO
+1
source
  • , ,

SELECT * FROM sys.schemas WHERE name = 't_one'

  1. t_one ,

CREATE SCHEMA t_one

  1. ,

USE <database_name>; GRANT CREATE SCHEMA TO <user_name>; GO

.

  1. If you have a scheme and you do not have permission to use it,

    USE <database_name>; GRANT CREATE TABLE TO <user_name>; GO

Then run the code to create the table.

Read the docs here

0
source

More articles:


All Articles