In spite of our vehement protests, our management has decided that the development team must be granted 'sa' rights on the development server. The catch is that we, the DB support group are still responsible for maintaining this box.
We have now been entrusted the task of coming up with a list of Dos and Don'ts for the development teams with these enhanced privileges.
Please add to this list:
DO —
confine activities to the DB under development
DO NOT —
- change any SQL instance settings
- sp_configure (including cmdshell)
- add/change/delete any security settings
- add/change/delete database objects
- add/change/delete server objects like backup devices and linked servers
- add/change/delete replication
- add/change/delete maintenance plans
- touch any database that doesn't belong to your team
Any pointers to tools available for tracking these users activities will be greatly appreciated.
Best Answer
If it's not too late, one compromise option that I've seen work well is rather than upgrade the permissions or replacing the developers' existing accounts, create a separate account that is only used when they need the elevated permissions.
So normally they work under individual "restricted" accounts (which I use loosely because these restricted accounts still need some hefty permissions — ie create, drop, alter for tables). But for that rare occasion when they think they need
sa, they can log in using this account. Then you can flag the account in your logs and do extra monitoring on it. You've given the developers the access they asked for, but in a way that's a little more controllable.Eventually, if there's abuse, the logs on this account can be used as evidence to take it away.