Appropriate SQL Server Permissions for Developers

Question

After a couple of Google searches and a quick look at questions here, I cannot seem to find what I thought would be a cookbook answer for SQL Server permissions.

As I often see in small shops, most developers here were using an admin account for SQL Server while developing. I want to set up roles and permissions that I can assign to developers so that we can get our jobs done, but also do so with the minimum permissions required. Can anyone offer advice on what SQL Server permissions to assign?

Components:

• SQL Server 2008
• SQL Server Reporting Services (SSRS) 2008
• SQL Server Integration Services (SSIS) 2008

Platforms:

• Production
• Staging/QA
• Development/Integration

We are running "Mixed Mode" security because of some legacy apps and networks, but are moving to Windows Auth. I am not sure if that really affects the role set up.

I plan to set up access for Developers to Prod and Staging/QA DBs as Read-Only. However, I still want developers to retain the ability to run Profiling.

We need Deployment accounts with higher privilege levels. We are currently trying to figure out exactly what privileges we need for SSIS package deployments.

Within the Development Server, Developers need broad privileges. However, I am not sure that just making them all admins is really the best choice.

It's hard to believe that no one has published a decent example script that sets up these kinds of roles with a good set of appropriate permissions for developers and deployers.

We can probably figure this all out by locking things down and then adding permissions as we discover the need, but that will be way too big a PITA for everyone.

Can anyone point me to, or provide, a good exemplar for permissions for these kinds of roles on these kinds of platforms?

Answer 1

This will vary widely from company to company. The key ingredient is to lock down production so that devs cannot create or change objects. Our devs only have datareader rights on prod, nothing else. They can't even execute a stored proc unless logged into the application and using the application's permissions.

We give pretty much full rights to many developers on dev, but it might vary depending on which datbases they are supposed to be developing against and which servers they are to access for the applications they support. So a dev with full access to one development server may not even have select rights on another.

By locking devs out of prod we have gained several critical things. First, there is no cowboy datbase development. They know they must create scripts for someone else to run and so they don't make random changes that they then forget about. THis also means they are not a problem about putting the scripts into source control, since the people who do have rights to prod will only run a script if it is in Source control.

Next there are no people making on-the-fly emergency, untested changes to prod, that never get down to dev and qa and are thus lost the next time a new version is loaded up. As a result the changes that didn't work on prod have gone way down as well because now everything is tested before someone tries to put it on prod.

Nor are people making on the fly data changes to prod and accidentally updating the entire user table because they forgot to highlight a where clause (yes this happened before we locked down prod).