Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

share|improve this question

4 Answers 4

up vote 8 down vote accepted

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.

share|improve this answer
    
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 '11 at 17:30
    
It definitely seems unfortunate that you have to grant blanket CREATE TABLE permission just to enable this scenario. –  MgSam Sep 17 '12 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]
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
share|improve this answer

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.

share|improve this answer

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

share|improve this answer
    
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 '11 at 17:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.