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


  • 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.

  • 1
    What tasks do they need to do that requires higher level privilages of SA?
    – Irwin M. Fletcher
    Oct 23, 2009 at 18:56
  • I would think that db_owner would be all they need...
    – OMG Ponies
    Oct 23, 2009 at 19:01
  • 5
    OK, if they're being advised to NOT "add/change/delete database objects", what are they supposed to do? Isn't the whole point of having a development box be that they can break it? Oct 23, 2009 at 19:01
  • These are application developers. They need 'sa' privileges to be able to debug stored procedures in Dev Studio. The decision is already made by the management and they are unwilling to budge. We are trying to minimize the potential headache
    – Raj
    Oct 23, 2009 at 19:07
  • 2
    +1 for the question, as I've hit that issue a couple of times. Another solution: Give the devs a sandboxed VM with SQL on it that they are 100% boss of. They are also 100% responsible for keeping it alive :) -> it is amazing how fast the DEVs learn not to break their own system :) Jan 6, 2012 at 19:03

8 Answers 8


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.


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'

  • 1
    The answer to developers who code outside the lines is to do continuous integration with locked down 'as will be run by the users' permissions, and fail the build accordingly. Have the build server recreate the database from scratch and populate it with test data (all from SCM of course). (In my case, I used exactly the same code as the application installer would use to create a new database, thus testing the installer as well as the application.)
    – Craig Trader
    Dec 22, 2010 at 22:12
  • 1
    @Craig trader: won't work. To rephrase my old answer, developers can't be trusted and integratiointegrationsn won't detect all cases. I'm a developer dba : a client code monkey is my enemy. In my corporate, I win.... its my trainset and I deal with lowest common denominator...
    – gbn
    Dec 22, 2010 at 23:19
  • Apparently, Your Mileage May Vary. In my experience, I've usually had to do a little training/orientation to explain how and why things are done, but after that things usually run fairly well. Of course, I always insist that the only builds that go beyond development are the ones that come from the tightly-controlled build server -- if the code doesn't build there, it's a problem with your code (or your tests) not the configuration. I've done this with small teams and large projects, across many companies, and always successfully.
    – Craig Trader
    Dec 27, 2010 at 8:01

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.


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.

  • Your analogy is completely wrong. What happens is they muck around and then whinge when they can't migrate their mucking around to locked down servers. I'm a developer DBA of course :-)
    – gbn
    Oct 23, 2009 at 19:28
  • Then a little education is in order. I'm a developer whose been a DBA all too frequently. With a foot in both worlds, it's usually been my job to teach both sides to coexist. After all, it's the USERs who are the enemy, not the DBAs or Developers...
    – Craig Trader
    Oct 23, 2009 at 20:05
  • There's also things like maintenance plans that they shouldn't have access to. Oct 23, 2009 at 20:17
  • 1
    The problem is, we have multiple Dev groups and this server host all their DBs. This group that requires 'sa' rights own only 33% of the Dbs. The concern is that they may screw around and bring the server down, impacting other groups as well. The company does not want to get seperate hardware for them, unless we are able to prove that they screwed up.
    – Raj
    Oct 23, 2009 at 20:28
  • 2
    That's ridiculous. Each development group should have their own server, so that no one steps on anyone elses toes. Hardware these days is practically free, and software almost as cheap as hardware. And if your organization is large enough to have multiple development groups, it should be using server virtualization, which would make all of this even easier.
    – Craig Trader
    Oct 23, 2009 at 20:52

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.

  • The problem here is that it's easy for developers to forget that the apps they build won't have the same rights all the time. Yes, give them sa, but make it so they aren't sa by default, so that they are aware when they are doing something that will need a little extra work to deploy properly. Feb 7, 2012 at 18:05

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.


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

  • Sorry about the confusion. What I meant by that bullet point was that developers must not change database settings or drop databases.
    – Raj
    Oct 23, 2009 at 19:24

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.


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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.