Don’t grant db_owner in SQL Server
24OCT
Don’t grant sysadmin, If you grant sysadmin you are making a HUGE mistake. Sysadmins can take over the server. Local admins can take over SQL. It does take some effort but Local Administrators and SQL Sysadmins effectively have the same privileges. When it comes to audit time, I believe you might be able to state these two roles as separate but behind the scenes they are the same.
But what about this db_owner? It looks fairly harmless. Lets take a closer look.
How bad is it?
As you can see, we have this new guy that wants to connect to SQL Server. We create him a login on an existing server with topsecret information (ooops #1). We create him a SQL Login jimmy_the_dev (ooops #2). But those two things alone are not all that bad. He can’t do much with just the public role.
Ahhh, I see, you wanted some actual privileges
We do have to grant jimmy some permissions. To do this we should create mappings from the login to a database. If you use the GUI it creates a database user automatically.
If you just select public, SQL creates the database user, but jimmy doesn’t have any useful permissions. He can’t even see the nice new ‘Table_1' we created for him. He is past the first error but not by much.
Where are my tables?
There are some convenient default database roles. db_datareader is a nice one that will grant read access to everything, including future objects. db_datawriter is the same but for IUDs. Some users will need to be manually granted execute permissions in the database so they can run stored procedures.
My recommendation is to create a few custom database roles and map to those. Unfortunately, time doesn’t always allow for that. New additions to the schema will need to be added to these roles. Maybe now you have time for this manual work now but when the schema changes you might not have that luxury.
The easy button for granting all access to a single database is the role “db_owner” The easy button is sometimes the wrong button to press. Here’s why:
1. a db_owner can create tables (1 trillion row heaps)
2. a db_owner can drop the database (dammit jimmy!)
3. a db_owner can backup the database and break the dba’s differential backups.
4. a db_owner can create users mapped to existing logins, and make them db_owners too!
5. a db_owner can perform maintenance like rebuilding 1 trillion row heaps
6. a db_owner can modify files and grow them to ridiculous sizes
7. a db_owner can add log files on the same drive as data files
8. a db_owner can turn off auto-growth
2. a db_owner can drop the database (dammit jimmy!)
3. a db_owner can backup the database and break the dba’s differential backups.
4. a db_owner can create users mapped to existing logins, and make them db_owners too!
5. a db_owner can perform maintenance like rebuilding 1 trillion row heaps
6. a db_owner can modify files and grow them to ridiculous sizes
7. a db_owner can add log files on the same drive as data files
8. a db_owner can turn off auto-growth
And maybe the best one of all, a db_owner can drop users… including themselves!
The suicide squeeze.
That said, these permissions are not too devastating. db_owner is not a “take over the world” role. I use it from time to time but the more experienced I get, the less I want to use it. The main reason is if you grant this, no one runs into security problems and they don’t understand how database security works. When it comes time to go live you might have to go through all of the security exercises you skipped by granting db_owner. That is more painful that doing it right in the first place.