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

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!

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.

StackExchange Database Administrators Q#2258, answer score: 3