Appropriate permissions for SQL Server for developers

Question

After a few google searches and a quick look at the issues here, I can't find what I thought would be the cookbook answer for SQL Server permissions.

As I often see in small stores, most developers here used an administrator account for SQL Server during development. I want to configure the roles and permissions that I can assign to developers so that we can complete our tasks, but also do so with minimal permissions. 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:

• Products
• Inclusion / QA
• Development / Integration

We are launching mixed-mode protection due to some legacy applications and networks, but we are moving on to Windows Auth. I'm not sure if this really affects the role setting.

I plan to configure read-only access for developers to the Prod and Staging / QA databases. However, I still want developers to keep 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 to deploy SSIS packages.

Inside a development server, developers need wide privileges. However, I'm 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 installs such roles with a good set of appropriate permissions for developers and deployers.

We can probably figure it all out by blocking things and then adding permissions when we discover the need, but that will be too much PITA for everyone.

Can someone point me or provide a good example for permissions for these roles on these platforms?

Answer 1

This varies greatly from company to company. A key component is production blocking so developers cannot create or modify objects. Our developers have only access rights to prod, nothing else. They cannot even execute the stored procedure unless they enter the application and use the permissions of the application.

We give quite a lot of rights for many dev developers, but this may vary depending on which databases they should develop and on which servers they should access the supported applications. Thus, a developer with full access to one development server may not even have the right to choose another.

By blocking the devs from prod, we got a few important things. Firstly, there is no development of a cowboy database. They know that they have to create scripts for someone else to run, and therefore they do not make random changes, which they then forget about. This also means that they do not pose a problem with installing scripts in the original control, since people who have rights to prod will only run the script if it is in source control.

Then there are no people who make "on the fly" urgent, unverified changes to prod that never come to dev and qa and thus get lost the next time the new version is downloaded. As a result, changes that did not work on prod also went down, because now everything is tested before someone tries to put it on prod.

In addition, people doing on-the-fly data changes in prod and accidentally updating the entire user table forget to highlight the where clause (yes, this happened before we blocked prod).

Answer 2

I was looking for similar recommendations, but could not find. After some experimentation, I think a simple setup would be to add the user to the dbcreator server role, and then add them to the "db_owner" role in each database that they will work on. This will allow the user to create new databases, as well as modify the ones in which they are members of "db_owner".