Home / server / Questions / 252808
Accepted
RyanW
Asked: 2011-03-29 08:36:50 +0800 CST

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

  • 772

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

sql-server permissions

4 Answers

  1. Best Answer

    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.

    • 13

  2. 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
    • 7

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

    • 1

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

    • 0