SQL Server Login Configuration and Automation

Question

What's a good way to manage and maintain SQL Server logins, server roles, and individual access rights across multiple databases that exist in multiple environments? What are your best practices?

Some info about my situation:

• SQL Server 2005
• We have N amount of "client" databases with identical schemas (in theory, at least)
• We have a central "admin" database that references each client database and can hold configuration values
• This "admin/client" pattern is duplicated across multiple environments (dev/qa/stage/prod)
• Some users, like testers, need different rights based on evironment
• We frequently have to pull client db backups from one environment to restore on another for development or testing purposes
• We keep our stored procedures and scripts in source control and deploy in a build cycle

Right now my organization is chaotic and we don't follow good security practices. We have no formal DBA. However, if we got any more complex it would be a constant hassle to maintain it all the time. I could see migrating to a new server or recovering from disaster being extremely time consuming if we where to attempt configuring it directly through the management studio IDE.

Answer 1

First, to make restoring a database to a different server easier, make sure that your logins all have the same SID on all of your servers by using the sp_help_revlogin stored procedure from Microsoft to script the login on the first server you create it on and then use the script to create the login on your other servers. This keeps the database user mapped to the login correctly when you restore the database.

Having different permissions at the database level depending on the environment is going to be a hassle to a point no matter how you role this out. I have a stored procedure in master that gets called on my Dev Server as a part of my restore process that performs the additional GRANT's on the database to give the developers access to make changes. That's the best I have been able to come up with to solve similar problems.

Answer 2

A way to make the rights easier would be to create rolls in the database called Dev, QA, Test, Prod and grant the correct rights to those roles. Then as you restore the databases to each environment just drop the developers in the correct role.

Answer 3

We use active directory groups and enforce windows authenticated logins. From within SQL Server we can then define access based on the AD group the user is in by creating a single SQL Server login per AD group. Not sure if this is any better or worse than DB roles, but it means the roles are managed outside each database.

Propagating access to databases is then either a manual operation or a short SQL script to ensure the logins in the database point to a valid SQL Server login (which in turn is an AD group).

Generally this works well for the general case. We can use DB roles then to assign the builtin roles (e.g, db_datareader) to each AD group

Rarely someone needs some specific access to a database outside this model. We either end up opening it up to the group as a whole if it's not going to be invasive or critical or we'll end up creating a per-user account that has to be managed separately. We endevour to keep these to an absolute minimum, and clean them up every now and then so they're not abused/forgotten about.