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.