Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 6 '12 at 18:03

This question came from our site for professional and enthusiast programmers.

1  
What tasks do they need to do that requires higher level privilages of SA? –  Irwin M. Fletcher Oct 23 '09 at 18:56
    
I would think that db_owner would be all they need... –  OMG Ponies Oct 23 '09 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? –  Stuart Ainsworth Oct 23 '09 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 '09 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 :) –  Martin S. Stoller Jan 6 '12 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.

share|improve this answer
    
+1: Excellent alternative approach! –  John Sansom Oct 23 '09 at 19:16

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'

share|improve this answer
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 '10 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 '10 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 '10 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.

share|improve this answer

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.

share|improve this answer
    
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 '09 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 '09 at 20:05
    
There's also things like maintenance plans that they shouldn't have access to. –  Joel Coehoorn Oct 23 '09 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 '09 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 '09 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.

share|improve this answer
    
+! that's the spirit! –  A-K Jan 6 '12 at 18:09
    
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. –  Joel Coehoorn Feb 7 '12 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.

question:

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

share|improve this answer
    
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 '09 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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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