Working with security in SQL Server 2008, I'm trying to grant a user CREATE TABLE permissions, but only within a specific schema. Does CREATE TABLE permission only apply at the database level?

Can I limit the user to create table only within a schema?

I've tried:

USE [databasename]
GRANT CONTROL ON Schema :: [schemaname] TO [username]


USE [databasename]
GRANT ALTER ON Schema :: [schemaname] TO [username]

But, the user is still unable to create a table within the target schema. It's not until I run this, hat the user can create a table:

USE [databasename]
GRANT CREATE TABLE to [username]


GRANT Database Permissions

4 Answers 4


According to SQL Server 2008 help:

Requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.

I just tested this by using EXECUTE AS in a test database, and I was able to create a table in the schema that had ALTER permission, but not in the dbo schema (for which I didn't grant ALTER permission). So as long as you haven't gotten too liberal with ALTER permission on schemas, this should be the way to do it.

  • 1
    Yes, I see that now. Even though the user has the database level "CREATE TABLE" permission, he us unable to create tables in schemas where no other permission has been granted.
    – RyanW
    Mar 28, 2011 at 17:30
  • It definitely seems unfortunate that you have to grant blanket CREATE TABLE permission just to enable this scenario.
    – MgSam
    Sep 17, 2012 at 13:06

Granting permission to create tables to a specific user in a specific database not only requires CREATE TABLE permissions but also requires ALTER permissions to the schema, e.g. DBO.

USE [databasename]
GRANT ALTER ON Schema :: [schemaname] TO [username]

For example;

USE db_mydatabase
GRANT ALTER ON Schema :: DBO TO user_mydatabase
GRANT CREATE TABLE TO user_mydatabase

There is nothing about such a permission in the documentation of permissions applicable to schemas in 2008 R2: http://msdn.microsoft.com/en-us/library/ms187940.aspx

Therefore I would conclude: no you can't, create table can only be controlled at a database level (this would appear to apply to all securables).

  • Thanks, I agree, there is nothing about that level. As @db2 has noted though, the database level "CREATE TABLE" permission is not enough to allow a user to create table if they have no other permissions on the schema.
    – RyanW
    Mar 28, 2011 at 17:32

Sure you can. If you have user1, then define its default schema as user1 also. Grant the CREATE TABLE in database permissions to this user. That's it. user1 can only create tables within the user1 schema.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .