With SQL Server (2008), can you grant create table permission within a schema?

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]
GO

and

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

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]
GO

GRANT

GRANT Database Permissions


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.


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]
GRANT CREATE TABLE TO [username]
GO

For example;

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

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).