Needs And Wants Are Different Things
Developers and analysts ask me all the time for a role in non production environments. Good ol’ db_owner on the database. It’s out of the box, and the developer sees it as their full understanding of what they need in order to do what they need to do on any given database and do their job. Well, I guess it’s one role, the other is sysadmin at the server level.
Developers are somewhat justified in asking for this role. It’s the only single role out of the box (minus sysadmin as I said) that allows you to perform ALL of the following tasks on a database:
CREATE/ALTER/DROP TABLECREATE/ALTER/DROP PROCEDURE (plus functions, views, etc)
Add indexes, statistics, and rebuild them
Script out existing code
View execution plans
All things that you would expect a database developer to want and or need to do to a database. I have no problem with any of these commands. I should note this is also the default that’s asked for application accounts that want to make changes through the application. So what’s my issue? Well, db_owner also allows you to do the following things:
HERE COMES THE PAIN TRAIN:
CHANGE RECOVERY MODEL
Did you have your database in the simple recovery model because you DIDN’T want to take transaction log backups on that non production box? Well, your db_owner role just hosed that and filled up a transaction log. That’s an easy fix I guess, unless you need to grow it out to get it to rollback, or you didn’t have the cap set properly on the log so it blew out the drive it was on. LOL GOOD TIMES.
I sincerely hope your default backup location has plenty of space. I also hope you’re not depending on a restore chain of any kind since they could literally backup the log somewhere only they have access to and delete it.
ALTER ANY SYMMETRIC KEY
You know that sensitive data you have in that database? You can’t get to it anymore.
TURN ON/OFF AUTOSHRINK
Oh boy here’s a fun one. “We ran out of space so we turned on autoshrink so we would have more room”. I hope that database isn’t getting put on prod anytime soon or is part of performance testing with all of that potential fragmentation.
“We ran out of space on the database so we added a new unlimited autogrowth datafile on the c drive. That file is out of space now too and the server isn’t responding for some reason.” or “We were told we’d get better performance with multiple log files” …I’ll just let you ponder those. It’s ok to laugh at them.
“Our test database is running slow but it was fine yesterday.” This one can come about as a delayed reaction since it might take a while to get some stale stats if you aren’t running maintenance on a set of stats due to the nature of the environment.
“Our application in UAT is acting all funny.” This one usually gets the vendor on the line or the architect if it’s built in-house.
I guess if you really wanted to screw up development work this would be the way to do it.
…and now for my favorite:
The funny part about this one isn’t that they can set the database to offline. It’s that they can’t bring the database back ONLINE. LOL!!! Why in the world would that ever be *needed*? I’ll hang up and listen.
Okay, So What In The World Are You Supposed To Give Them?
Well, if you want to keep it out of the box, give them db_datareader, db_datawriter, and db_ddladmin, and then give them execute as an explicit permission. That can be a lot of work. Instead, create your own role, db_developer. I recommend setting this custom role up on model and giving it these rights:
CREATE ROLE [db_developer] AUTHORIZATION [dbo]
GRANT ALTER ANY APPLICATION ROLE TO [db_developer]
GRANT ALTER ANY ASSEMBLY TO [db_developer]
GRANT ALTER ANY DATABASE DDL TRIGGER TO [db_developer]
GRANT ALTER ANY DATASPACE TO [db_developer]
GRANT ALTER ANY FULLTEXT CATALOG TO [db_developer]
GRANT ALTER ANY MESSAGE TYPE TO [db_developer]
GRANT ALTER ANY SCHEMA TO [db_developer]
GRANT CREATE AGGREGATE TO [db_developer]
GRANT CREATE ASSEMBLY TO [db_developer]
GRANT CREATE DATABASE DDL EVENT NOTIFICATION TO [db_developer]
GRANT CREATE DEFAULT TO [db_developer]
GRANT CREATE FULLTEXT CATALOG TO [db_developer]
GRANT CREATE FUNCTION TO [db_developer]
GRANT CREATE PROCEDURE TO [db_developer]
GRANT CREATE ROLE TO [db_developer]
GRANT CREATE RULE TO [db_developer]
GRANT CREATE SCHEMA TO [db_developer]
GRANT CREATE SERVICE TO [db_developer]
GRANT CREATE SYNONYM TO [db_developer]
GRANT CREATE TABLE TO [db_developer]
GRANT CREATE TYPE TO [db_developer]
GRANT CREATE VIEW TO [db_developer]
GRANT CREATE XML SCHEMA COLLECTION TO [db_developer]
GRANT DELETE TO [db_developer]
GRANT EXECUTE TO [db_developer]
GRANT INSERT TO [db_developer]
GRANT REFERENCES TO [db_developer]
GRANT SELECT TO [db_developer]
GRANT SHOWPLAN TO [db_developer]
GRANT UPDATE TO [db_developer]
GRANT VIEW DATABASE STATE TO [db_developer]
GRANT VIEW DEFINITION TO [db_developer]
This role *should* get them everything they need without the bad implicit permissions I listed with the db_owner role. One caveat: If they have this role and try to use the table designer feature in SQL Server Management Studio, they might get an error saying they can’t use it; however if they “OK” out of the error it will take them to the tool anyway. If you don’t like some of the explicit permissions in this role, remove them! You should know what implicit permissions all roles have, whether you’re a database administrator or a developer.
Follow the principle of least privilege and setup a custom role for application accounts so they only have the specific rights they need to the objects they will use and nothing more.
Let me know what you think!