Can db_ddladmin role permissions be limited to specific tables on a schema?

  • We are running SQL Server 2012 SP3 on Windows 2008 Server. Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database. I need to give a user the create and drop permissions to specific tables (which are created by the same user) on a vendor  schema. This is a rare (vendor related) need for a user to create and drop tables on the vendor schema. I am trying to figure out a way to do it without given the user db_ddladmin role permissions to the entire vendor database and schema. 

    Can the db_ddladmin role permissions be limited to specific tables on a specific schema? I don't want a member of db_ddladmin role to be able to perform DDL commands on all of the tables on the vendor schema. 

    Thanks for any suggestions and/or help. 

  • HookSqlDba7 - Sunday, September 16, 2018 7:53 PM

    We are running SQL Server 2012 SP3 on Windows 2008 Server. Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database. I need to give a user the create and drop permissions to specific tables (which are created by the same user) on a vendor  schema. This is a rare (vendor related) need for a user to create and drop tables on the vendor schema. I am trying to figure out a way to do it without given the user db_ddladmin role permissions to the entire vendor database and schema. 

    Can the db_ddladmin role permissions be limited to specific tables on a specific schema? I don't want a member of db_ddladmin role to be able to perform DDL commands on all of the tables on the vendor schema. 

    Thanks for any suggestions and/or help. 

    It depends on the requirements. If it's essentially static with specific tables, this may be something where you can create a stored procedure to perform the activities. You can sign the stored procedure or use execute as.

    Sue

  • HookSqlDba7 - Sunday, September 16, 2018 7:53 PM

    We are running SQL Server 2012 SP3 on Windows 2008 Server. Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database. I need to give a user the create and drop permissions to specific tables (which are created by the same user) on a vendor  schema. This is a rare (vendor related) need for a user to create and drop tables on the vendor schema. I am trying to figure out a way to do it without given the user db_ddladmin role permissions to the entire vendor database and schema. 

    Can the db_ddladmin role permissions be limited to specific tables on a specific schema? I don't want a member of db_ddladmin role to be able to perform DDL commands on all of the tables on the vendor schema. 

    Thanks for any suggestions and/or help. 

    Sue touched on it... You don't need to give the user any privs except to execute the stored procedure that does these things and operates with the EXECUTE AS OWNER directive.  Make sure that the procs limit the actions to only those tables that the user is authorized to drop and recreate.

    As a bit of a sidebar, rather than dropping and recreating, why wouldn't "TRUNCATE TABLE" of existing tables work?  Yes, you'd still need to use a stored proc because TRUNCATE TABLE is DDL but it seems that it make life a whole lot simpler.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Sue and Jeff for your suggestions.

  • Jeff Moden - Monday, September 17, 2018 9:36 AM

    HookSqlDba7 - Sunday, September 16, 2018 7:53 PM

    We are running SQL Server 2012 SP3 on Windows 2008 Server. Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database. I need to give a user the create and drop permissions to specific tables (which are created by the same user) on a vendor  schema. This is a rare (vendor related) need for a user to create and drop tables on the vendor schema. I am trying to figure out a way to do it without given the user db_ddladmin role permissions to the entire vendor database and schema. 

    Can the db_ddladmin role permissions be limited to specific tables on a specific schema? I don't want a member of db_ddladmin role to be able to perform DDL commands on all of the tables on the vendor schema. 

    Thanks for any suggestions and/or help. 

    Sue touched on it... You don't need to give the user any privs except to execute the stored procedure that does these things and operates with the EXECUTE AS OWNER directive.  Make sure that the procs limit the actions to only those tables that the user is authorized to drop and recreate.

    As a bit of a sidebar, rather than dropping and recreating, why wouldn't "TRUNCATE TABLE" of existing tables work?  Yes, you'd still need to use a stored proc because TRUNCATE TABLE is DDL but it seems that it make life a whole lot simpler.

    One thing I've noticed with TRUNCATE TABLE is that if a user needs to execute that code, they need to have the requisite security permissions in SQL Server. TRUNCATE TABLE is a horrible solution if you don't want to give those permissions. You need to use DELETE FROM <table> without a WHERE clause in order for users to "truncate" without having TRUNCATE TABLE permissions.

    EDIT: I should note that we don't tend to add EXECUTE AS OWNER permissions to a lot of procs. That in itself is a maintenance headache if the owner is a user that leaves the company and you certainly don't want to make the owner any service account. DELETE FROM removes the maintenance headache and any potential security issues. FYI: I recommend adding a code note that the lack of WHERE is on purpose for future reference when you or other people are troubleshooting.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin - Monday, September 24, 2018 5:50 AM

    Jeff Moden - Monday, September 17, 2018 9:36 AM

    HookSqlDba7 - Sunday, September 16, 2018 7:53 PM

    We are running SQL Server 2012 SP3 on Windows 2008 Server. Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database. I need to give a user the create and drop permissions to specific tables (which are created by the same user) on a vendor  schema. This is a rare (vendor related) need for a user to create and drop tables on the vendor schema. I am trying to figure out a way to do it without given the user db_ddladmin role permissions to the entire vendor database and schema. 

    Can the db_ddladmin role permissions be limited to specific tables on a specific schema? I don't want a member of db_ddladmin role to be able to perform DDL commands on all of the tables on the vendor schema. 

    Thanks for any suggestions and/or help. 

    Sue touched on it... You don't need to give the user any privs except to execute the stored procedure that does these things and operates with the EXECUTE AS OWNER directive.  Make sure that the procs limit the actions to only those tables that the user is authorized to drop and recreate.

    As a bit of a sidebar, rather than dropping and recreating, why wouldn't "TRUNCATE TABLE" of existing tables work?  Yes, you'd still need to use a stored proc because TRUNCATE TABLE is DDL but it seems that it make life a whole lot simpler.

    One thing I've noticed with TRUNCATE TABLE is that if a user needs to execute that code, they need to have the requisite security permissions in SQL Server. TRUNCATE TABLE is a horrible solution if you don't want to give those permissions. You need to use DELETE FROM <table> without a WHERE clause in order for users to "truncate" without having TRUNCATE TABLE permissions.

    EDIT: I should note that we don't tend to add EXECUTE AS OWNER permissions to a lot of procs. That in itself is a maintenance headache if the owner is a user that leaves the company and you certainly don't want to make the owner any service account. DELETE FROM removes the maintenance headache and any potential security issues. FYI: I recommend adding a code note that the lack of WHERE is on purpose for future reference when you or other people are troubleshooting.

    That's why you must NEVER allow the owner of a database to be an individual.  It removes all such "maintenance headaches" and make the use of "EXECUTE AS OWNER" to do simple and wonderful things, such as TRUNCATE TABLE, simple, sure, and consistent.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply