Any pointers on reducing my privileges in production but not making my job excessively difficult

Question

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!

Answer 1

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:
http://msdn.microsoft.com/en-us/library/ms187611.aspx

Answer 2

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.

Answer 3

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.