September 16, 2018 at 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.
September 17, 2018 at 7:49 am
HookSqlDba7 - Sunday, September 16, 2018 7:53 PMWe 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
September 17, 2018 at 9:36 am
HookSqlDba7 - Sunday, September 16, 2018 7:53 PMWe 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
Change is inevitable... Change for the better is not.
September 19, 2018 at 11:24 am
Thanks Sue and Jeff for your suggestions.
September 24, 2018 at 5:50 am
Jeff Moden - Monday, September 17, 2018 9:36 AMHookSqlDba7 - Sunday, September 16, 2018 7:53 PMWe 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.
September 24, 2018 at 6:26 am
Brandie Tarvin - Monday, September 24, 2018 5:50 AMJeff Moden - Monday, September 17, 2018 9:36 AMHookSqlDba7 - Sunday, September 16, 2018 7:53 PMWe 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
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply