CODEWITHSUNDEEP
sqlsql-serversql-server-2008
jn025
jn025

Reputation: 2895

SQL server unable to create table in database because of permissions

I am using SQL server 2008 Express R2. I was trying to test a couple of queries 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 to my user profile just using the interface and after i clicked OK/save it didn't apply them - when I went back to the permissions for my user they were all unchecked again.

Upvotes: 6

Views: 31485

Answers (3)

jophab
jophab

Reputation: 5509

  1. To check whether the schema exists or not, try

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

  1. If the schema t_one not exist, try running

CREATE SCHEMA t_one

  1. If you don't have permission to create Schema,

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

Then create schema and run the code for creating the table.

  1. In the case you have the schema and you are not having the permission to use it,

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

Then run the code to create table.

Read Docs here

Upvotes: 0

tinonetic
tinonetic

Reputation: 8036

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

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

Upvotes: 1

Roman Pokrovskij
Roman Pokrovskij

Reputation: 9766

Try to run it this way:

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)

)

Upvotes: 19

Related Questions