none
Restrict Role to CREATE TABLE on an assigned SCHEMA

    Question

  • Can anyone help me with this? The objective is to allow an application user (with db_datareader & db_datawriter database roles assigned) to be able to create tables in the assigned schema (dbo) via a new role.  

     

    -- Create User

    use master

    go

    create login DBA with password='xx', CHECK_EXPIRATION=ON, CHECK_POLICY=ON

    use AdventureWorks

    go

    create user dba from login DBA

    alter user dba with DEFAULT_SCHEMA=dbo

    go

     

    -- Create Role

    use AdventureWorks

    go

    create role sp_ddl_role AUTHORIZATION dbo

    grant CREATE TABLE to sp_ddl_role

     

    -- assign user to Role

    use AdventureWorks

    go

    exec sp_addrolemember sp_ddl_role, dba

    go

     

    -- Create Table statement then run with following error

    Error:

    Msg 2760, Level 16, State 1, Line 1

    The specified schema name "dbo" either does not exist or you do not have permission to use it.

     

    Remedy: Grant ALTER on schema::dbo to sp_ddl_role

     

    My problem is that I want to restrict user permissions via this role to just the CREATE TABLE and granting ALTER on a SCHEMA will open up a whole lot more permissions from a security standpoint.

     

    Question: How do I restirct this role to just the CREATE TABLE within a SCHEMA? 

     

     

    Friday, May 09, 2008 10:19 AM

All replies

  • You can also issue REVOKEs to achieve to your goal in combination of roles.

    Sample:

     

    Code Snippet

    REVOKE VIEW DEFINITION FROM MaryS CASCADE;

     

     

    Friday, May 09, 2008 1:40 PM
    Moderator