Hi, I would like to know the best practices followed for role based access in SQL Server...
I have a situation now each and every DBA/Developer has Sysadmin/DBA rights.. I would like to restrict the access based on their roles and responsibilities...
asked Feb 14 at 08:05 PM in Default
By all means to do. Hopefully that isn't in a Production environment because that's just calling for a disaster to occur.
Usually, the best practice is to limit them to whatever they need, which follows the Principle of Least Privilege.
I would look at what they really need (most likely NOT SA rights) and then limit them down to that. It might be read/write/view definition/exec on particular databases or all databases. Usually you can script out these types of changes to make sure things are consistent. Use the database roles (either built-in or create your own) to further help with this.
I would also look at utilizing active directory to help facilitate this. It's much easier to give access to a domain group and then assign permissions to that group. That way as people come and go they just get added to the appropriate group(s) and permissions are already handled.
For a production environment, I personally don't like anything but service/application accounts to have write/exec permissions so that I can control who has the ability to change data. There are those that will even limit read permissions to help reduce the workload on a production box.
Hope that helps!
answered Feb 14 at 08:24 PM