Best Practices for role based access in SQL Server

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...


more ▼

asked Feb 14, 2017 at 08:05 PM in Default

avatar image

11 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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!

more ▼

answered Feb 14, 2017 at 08:24 PM

avatar image

14.5k 3 7 15

Hi John,

Thanks a lot... .the requirement is across the environment ( production/lower environments )... Do you think it's better to had 3 dedicated account ( thru AD), which has full rights, read and read/write permission.


Feb 14, 2017 at 10:29 PM kar2411

For service accounts, yes. Personally, I like having a service account for each environment. Production is my main concern so I try to isolate that as best as possible. If you share service accounts across all environments, there are ways that it can reach up and touch production unexpectedly, say via linked servers. There are ways to account for that but isolating Production as best as possible is always my advice.

User accounts I tend to not grant anything but read permissions in production, the lower environments I might be less strict on what they can do.

Hope that helps.

Feb 14, 2017 at 10:41 PM JohnM

This helps me a lot.


Feb 15, 2017 at 06:29 PM kar2411
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 14, 2017 at 08:05 PM

Seen: 73 times

Last Updated: Feb 15, 2017 at 06:29 PM

Copyright 2018 Redgate Software. Privacy Policy