none
How to Prevent db_owner from Changing His Database Role Membership for a Database

    Question

  • Recently I created a Login for a developer with db_owner for a particular database.  What I did not know was that as a db_owner, he can go in and assign other roles.  For example, he went in and alter his Login to include

    db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_securityadmin.

    What can I do to prevent a user from doing this? 


    lcerni

    Thursday, July 25, 2013 5:22 PM

Answers

  • >What can I do to prevent a user from doing this? 

    Don't use db_owner.  Create a new role, grant the appropriate permissions and add the user to that.

    You can grant permissions on the database or schema level so there's only a handful of GRANTs to manage, and you don't have to change them as you add objects to the database.

    Here's how to create a role that has full DML across the whole database, and the ability to perform DML in the dbo schema:

    create role app_user
    
    grant select, insert, update, delete, execute to app_user
    
    grant create table, create view, create procedure, create function to app_user
    
    grant alter on schema::dbo to app_user

    Davd


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, July 25, 2013 5:43 PM
  • Many database objects (tables, views, procedures, and some others types) are grouped into schemas. When you execute a statement such as SELECT name FROM dbo.Employees; dbo is the schema. Granting ALTER on the schema, in this case dbo, allows a user to add, change, or drop objects in that schema. So GRANT ALTER ON SCHEMA::dbo TO user1; would let user1 create tables, change the definition of views, or drop stored procedures, in that schema. To create a object (such as a table) in a schema (such as Sales), the user needs CREATE TABLE permission and ALTER SCHEMA::Sales permission.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by lcerni Friday, July 26, 2013 5:56 PM
    Friday, July 26, 2013 2:40 PM

All replies

  • Hi,

    Well, that's a direct consequence of db_owner's nature : it can modify the database at will.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Thursday, July 25, 2013 5:36 PM
  • >What can I do to prevent a user from doing this? 

    Don't use db_owner.  Create a new role, grant the appropriate permissions and add the user to that.

    You can grant permissions on the database or schema level so there's only a handful of GRANTs to manage, and you don't have to change them as you add objects to the database.

    Here's how to create a role that has full DML across the whole database, and the ability to perform DML in the dbo schema:

    create role app_user
    
    grant select, insert, update, delete, execute to app_user
    
    grant create table, create view, create procedure, create function to app_user
    
    grant alter on schema::dbo to app_user

    Davd


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, July 25, 2013 5:43 PM
  • Thanks. 

    I am not sure I understand what grant alter on schema does?  Can you give me a short explaination?  I don't understand Microsoft's web page on this subject.


    lcerni

    Friday, July 26, 2013 1:49 PM
  • Many database objects (tables, views, procedures, and some others types) are grouped into schemas. When you execute a statement such as SELECT name FROM dbo.Employees; dbo is the schema. Granting ALTER on the schema, in this case dbo, allows a user to add, change, or drop objects in that schema. So GRANT ALTER ON SCHEMA::dbo TO user1; would let user1 create tables, change the definition of views, or drop stored procedures, in that schema. To create a object (such as a table) in a schema (such as Sales), the user needs CREATE TABLE permission and ALTER SCHEMA::Sales permission.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by lcerni Friday, July 26, 2013 5:56 PM
    Friday, July 26, 2013 2:40 PM