Why you should be cautious with the db_owner and db_ddladmin database roles

How many times have you heard the request by the business that an individual or individuals require either the db_owner or db_ddladmin privilege for their database.
 
As a DBA you need to understand the consequences of this type of request.
 
This may make the requestors job a lot easier, but did you realise you’ve potentially handed over the SQL server?
 
How is that possible?
 
Here goes.
 
1. Business approves for AUSER to be added to the db_owner role (or db_ddladmin  role) of ADATABASE
 
2. DBA grants the privilege
 

 

3. AUSER connects and creates a table in the database with an index
 

 

4. AUSER creates a trigger on the database
Update: Thanks to Pawel Wojtowicz for pointing out that the following code doesn’t work on SQL 2008 R2. It has been tested on SQL 2012/4. You would also tend to use the new ALTER SERVER ROLE instead of sp_addsrvrolemember.
 

 
The following works on SQL 2008 R2, but the maintenance job shown later in this post will fail when run.
image201408

 

5. Lets confirm that AUSER is not a sysadmin member on the server
 

 

6. AUSER now waits for the DBA to do their job i.e. INDEX maintenance. The DBA likely has a maintenance job of some type that runs under sa or elevated credentials as shown below
 

 

7. The maintenance plans runs.  AUSER’s is now a sysadmin and it’s their server.
 

 

If you didn’t know about this, you do now.
 
As always, ensure you have policies and monitoring in place to address this scenario.

Steve Davies

Kevin – thank you for your excellent post, we use DDLAdmin to give people limited control over their own databases rather than full DBO and the hack you described does work. I will now change the permission profile to either disable trigger creation or just give the individual permissions within the DDLAdmin group. You could pass this on to Brent Ozar or MidnightDBA to get the message our there. Well Done!

Leave a Reply to kevin Cancel reply