SQL server unable to create table in database because of permissions

Question

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.

Answer 1

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)

)

Answer 2

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