Designing Database access levels At the moment our production and development databases give sysadmin access to all of devops, third party developers, etc.
I'd like to propose new access levels based upon existing AD groups, but I'm wondering if anyone can give pointers on what levels of access should be defined?

At the moment I've come up with 3 roles:


Admin - giving sysadmin access 
Developer - giving permission to create/drop tables, insert/update/delete.
Viewer - read only access.


We also have different applications running on each database/server, and it was proposed that we don't separate these by access level. So, if one person has sysadmin on the 'Sales' database, they would also have the same level on 'Human Resources', etc.

It was also suggest that that our new AD groups would be server agnostic, to ease in transitioning new application access. 

I'm wondering if anyone can give their opinions how you've defined your access levels, etc.

Thank you.
Having generalized security across all servers / applications would be a bad approach. With your suggestion, once you let John Doe from Customer Service have read access the Customer database (which he needs in order to accomplish his job), you effectively give him read access to job applicants in an HR database. The HR database would contain Personally Identifiable Information (PII). Since John Doe is not apart of HR, he shouldn't have the permissions to access this data. Allowing him to read out of the HR database would cause compliance issues and could result in major fines when those auditors bust through the door! 

John Doe would also be able to read salaries out of the Payroll database, and ohh boy, once that info goes public you're bound to have some tension in the office; employees will question why the new guy is being paid more than them, etc. 

Now that I've beat that horse, here is an article I ran across recently discussing how to setup AD groups with specific levels of access to specific databases. This goes into a little bit more detail than you may need, such as creating scripts to create the AD groups, but should be a helpful start.

http://www.sqlservercentral.com/articles/Security/133778/