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?