Running SQL Server 2005 and 2008 on Windows 2008 R2.

We're going to be reducing privileges in production for developers - and I'd like to do the same for myself as a DBA, limiting rights to production and elevating when required.

My primary goal would be to eliminate stupid mistakes - made by DBAs, devopers will have read access in production at the most. We like to act like we are superheroes that cannot make a mistake, but not having production rights all the time makes sense and it a best practice, recommended by some.

What's the best approach? What will be least painful to use day to day and during installations?

We currently have a windows group for DBAs that has rights to all of our servers and databases.

I'd also be interested in lowering OS / remote login permissions - but I'm most concerned with DB rights.

I'm guessing we'd need elevated privs to run traces as sa, and possibly to some ownership cleanup before we take away our old login's SA rights. What other problems might we expect?

Thanks for your advice and sharing your experiences!

  • What database platform(s) are you using (SQL Server, Oracle, DB2, MySQL, etc.)? Are you talking about database privileges? Or operating system privileges? Apr 19, 2011 at 16:02
  • That would help, eh? SQL Server 2005/2008. Interested in both DB and OS privs.
    – Sam
    Apr 19, 2011 at 16:43
  • What kind of stupid mistakes are we talking about here? The single most valuable safety mechanism I have found is to set the desktop background on all production machines to red with PROD in yellow letters. Because in my long experience "security" measures that just annoy people will simply be worked around, and when there's a crisis, will just slow you down. You really don't want to be in the position where you do need the sa account and no-one can remember the password...
    – Gaius
    Apr 19, 2011 at 20:14
  • Yes, I have my desktop set to red on servers and green on dev. I'm thinking about data modification mistakes in SSMS mostly.
    – Sam
    Apr 21, 2011 at 19:59

3 Answers 3


Ideally, for an operational production database, you don't want developers having any access to the server or any database on it at all. That sort of thing is one of the first things you'll have to do for SOX compliance.

For the sort of rights that userIDs run under, the only rights they really should have are db_datareader, db_datawriter and explicit GRANT EXECUTE ON x TO y (for each stored proc and user defined function x for the userID y).

If you need to be running traces in production, you have some problems and it will take a Great Wall of Text™ to explain it all. My first recommendation is to have a QA environment that is locked down just like production and if traces need to be run, restore a back-up of the prod db to QA and run the traces there. Again, if you have SOX, HIPAA or PCI-DSS requirements, then you better sanitize the prod data before restoring it to QA.

We currently have a windows group for DBAs that has rights to all of our servers and databases.

Give them logon and view data rights; however to perform DBAly duties, use a separate login with elevated privileges. I know one financial customer that does this - the regular windows authentication based logins were limited in the damage they could inadvertantly do. Restores and running DML required running with the separate SQL authentication login.

One government agency I worked with used 2 separate logins for each server/db admin. So if Tangurena was my domain login (this login would have regular User privileges), then TangurenaAdmin would be my separate Administrator login. You get into trouble if you use your admin account all the time, but then it lacks permissions to other things (like no email. Oh, you say that like it is a bad thing...).

The current government agency I'm working with has each server/db admins having privileges elevated above the standard user, but not quite admin (think of it as the PowerUser group). Domain admin functions are performed with a shared domain admin account.

A common error is restoring the wrong database (like QA restored over the production server), and this isn't going to be solved via restricted rights or multiple logins. Doing potentially destructive things in pairs is one way to minimize the risks.

I'm guessing we'd need elevated privs to run traces as sa

No. You only need ALTER TRACE permissions:

  • Please read the bold section in the question.
    – Sam
    Apr 21, 2011 at 20:03
  • Thanks for supplying a good answer - and specifics from your past. Much appreciated.
    – Sam
    Apr 25, 2011 at 16:34
  • Do you mean ALTER TRACE perhaps?
    – Sam
    Apr 25, 2011 at 20:58
  • @Sam, fixed....
    – Tangurena
    Apr 25, 2011 at 22:06

At first, I suggest you do all the privilege play in a development or QA environment where there is no problem if the access is removed for some time. You will need to see if the applications won't have any issues with security.

I'll tell you our internal approach:

  • all the applications use a single domain user that is granted necessary permissions on a database (usually db_owner database role).

  • for occasional data read we use a SQL login. For that user we assign the database role - db_datareader. This is where it ends the access for developers on the main database cluster. For any other idea they would have, they will use the reporting server databases which are copies (done using Log Shipping) of the main server databases done at midnight. In order not to kill the reporting server with killer ad hoc queries we use the resource groups allocations for memory and cpu.

  • for the DBA team we have a domain group which has all privileges on the machine and the server (admin on the windows machine and sysadmin on the sql server)

  • for installations we have a SQL user that is db_owner on the databases that we use when running the updates - we use DDL triggers to monitor schema changes and we should see which changes were done during installation or as separate change

  • there are some occasional exceptions for experienced developers, but after their need is fulfilled we remove their access - they receive permissions based on their domain login, so we can monitor connections in traces/ddl views and any possible changes with the ddl triggers.

As for the way of doing all that work with the logins and users - in Management Studio in the server security folder you create all the needed logins, and then, you associate them with your databases and give them the roles they need. If you script the action you will see that initially it will be created a server login, then a database user connected to that login, then assigned a database role for that user. You can keep the script in your script set, so you can verify every time which users should be live and kicking and which should not be.

  • Please re-read the question. None of you are getting even remotely close.
    – Sam
    Apr 21, 2011 at 20:11
  • I'd say we answered on subject, because only a strong policy will keep you safe. Even you, as DBA, will be able to know what you did and when. For usual operations, use your user or the read-only user, for installation purposes use a specific user, for developers only a read-only user, for general action monitoring - DDL triggers and traces. What's wrong in this line of action? I don't think there is any way of automating the privileges elevation as you seem to want. Even operating systems have this usage, low privilege users for usual operations and high powered users for admin actions.
    – Marian
    Apr 22, 2011 at 18:36

In SQL server you can create a database user and assign it a database role with read/write/ownership permission(s). Once the user is migrated to production, you can go to the database user that was migrated and un-check the roles you do not want it to have. For example, lets say the user stan is a member of db_owner (ownership of the database) in test. Once the user stan is migrated to production, you can take it out of db_owner and assign only a role of db_datareader (read only) to it.

In SQL 2005+ more granular control can be done with the schema. Check this link on schema for more details.

  • Please read the bold section in the question.
    – Sam
    Apr 21, 2011 at 20:01

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.