This month’s blog party for T-SQL Tuesday is hosted by Kenneth Fisher (b|t) and poses the question to us “How do you manage security?”. As with any DBA worth their salt, security is near and dear to me. It is always at the fore front of my mind.
When we’re talking security, the first thing that comes to mind is, “protect the data”. The second is, “grant only what will get the job done.” This is what I call “least” access level. What this means is give the user/application the minimum access needed to get the job done. Why place the user in the dbo owner group when what they only need to read data?!
There are a great many 3rd party application that “require” SA access, when a much lower access is all that is needed. These applications take the easy way out in that it is much easier to just grant SA rather than granting data reader and dbExecutor.
This is why in my shop I have instituted group level access. On my server I have the following local windows sql_DB_DataReader, sql_DB_DataWriter, sql_DB_DBO, sql_DB_SA, which I then place Active Directory groups or individual users. I then create Windows Logins for these groups on my server and grant the appropriate database or server role. If there is a need for a group to view the objects of the database I grant that right separately and again it is granted to the local Windows group.
Using this type of group level policy I can control who gets access and what they are allowed to do very easily. It take a bit of time to go through who gets what access but once that has been determined all I have to do is drop the users in one of my local groups and bing, bang boom – security is all addressed.
There are as many ways of configuring security as there are DBAs, I hope what I’ve explained for this blog party allows you to setup your security as quickly and simply as I have.