Granting SA privileges for Developers on the development box

Question

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.

Answer 1

I do not think that they need SA privileges on the Development box. In almost all cases, they can do without.

I think a good option is to have local dev edition installed.

question:

You do not want developers to add/change/delete database objects?!! How are they going to develop?

Answer 2

SQL Server Auditing is what you want, but, if you will do all the database object creation for them, why do devs need sa?

http://msdn.microsoft.com/en-us/library/cc280526.aspx

Answer 3

On suggestion I have is to set up Policy-Based Management and enforce all your 'do' and 'don't' as policy rules. This would go a long way to protect the instance.

I would also deploy DDL change auditing, see Auditing in SQL Server 2008, not so much as a deterrent, but mostly as a change tracking system so when something is screwed, at least you'll know what changed.

Answer 4

It says here (MSDN) you need sysadmin (sa) to debug on SQL Server 2005.

However, this SO question shows another way without sa , which is what I thought initially. Simply allow them to run sp_sidedebug

I'd also suggest giving them local SQL Express which also solves other issues...

(edited with more info)

Edit, after W. Craig Trader's answer

Other issues with "sa" rights, in the worst case:

• developers will create untrusted CLR assemblies
• they will use xp_cmdshell
• all actions are in the context of the sql server service account
• they will assume sa rights for the client connection
• etc etc

eg xp_cmdshell 'scm -Action 6 -Server PRODSERVER'

Answer 5

If it's the DEVELOPMENT server, what's the problem with the DEVELOPERS being able to have full access? Telling developers that you can't add/remove/change database objects (eg: tables, columns, indexes) is like telling them "You can have a compiler, but you aren't allowed to run it". It would seem to me that the developers want/need access to their own database instance specifically to allow them to test different methods of solving problems WITHOUT having to muck with the PRODUCTION or TEST databases. You should be encouraging that sort of behavior, not discouraging it.

Some might suggest that developers work with local instances of SQL Express, but while SQL Express for each developer can solve certain problems, it has different limitations and performance characteristics than full SQL Server on a separate server.

What you SHOULD do is institute a regular backup schedule (at least nightly) and work with the developers to ensure that they know how to initiate unscheduled backups, and restore from backups, so that downtime is minimized in the event of problems.

Answer 6

Its the dev server, not the DB support group server, or the production server. Keep a good backup/image and let the devs hack away. Letting DBA's control the devbox is like letting the tail wag the dog. Its there for developers to do developer work on. This involves breaking things sometimes and dropping tables, and then putting them back with different settings. Dev boxes are always going to be in a state of disrepair after awhile, that's what we do. If we don't know where a problem is occurring, we try different things out. Some of them are easy to undo, some not so much.

Answer 7

We require all database structure changes to be done with scripts (even on dev) and saved in subversion. Then on a set schedule we refresh dev from prod and they have to rerun their scripts to get back to where they were in the development cycle. This helps ensure that everything is done through scripts and that they have scripts ready when it's time for deployment.

I know in 2008 you can set up DDL Triggers to track database structural changes, can you do this in 2005? This way at least you can find out when someone changes a setting who did it and find out why.