Do we have SQL specific AD groups? And add those groups to higher groups for job classification?
Security in SQL Server is actually a fairly simple model. There are logins to the server, which are mapped to users in a database. You can assign rights to both levels (depending on the permission) and you can group users into roles. However it starts to get complicated from there pretty quickly. We now have certificates that can log in with no login, schemas, and more in SQL Server 2008.
In the past, the guidance that I have always seen is that you should group users as much as possible and assign rights in batches. It's a practice I have always followed, never assigning rights to users, even if it's one user. I always create a role and assign the role permissions. As I've learned many times, you never know when you'll have to add those rights to another person.
People still ignore that advice and find themselves performing more administration than necessary, but in researching an answer to a question, I wondered about this one scenario for this Friday's poll:
Should we have separate AD groups for SQL Server permissions, even if we duplicate another AD group?
Here's the sceanario. Suppose that you have an HR group that has workers doing one job and managers doing slightly different functions. In AD you setup two groups that have rights for the various things they need (Exchange, file system, etc.). Now you need to add these users to a SQL Server for HR work. The easy thing to do is reuse those AD groups (HR_Workers, HR_Managers) as logins, create two roles in the HR database, and assign each Windows group to the appropriate role.
But is that good enough? What if you have a need to let an HR worker do some specific function in SQL Server? I've seen this before with people cross training, maybe semi-promoted to a "lead" role, or some other thing. Do you make a third AD group and a third SQL role? Or should you have already had 6 AD groups (HR_Workers containing HR_Workers_AD and HR_Workers_SQL and corresponding manager roles). Would that simplify things in that you could still move add the HR_Workers_SQL role to someone else without giving them the file system permissions if that's not appropriate?
I tend to think this is too complicated, but I haven't done much AD work in a long time and I'm wondering what other people do. Do you duplicate existing AD groups for SQL Server specific functions or just reuse what's there? Let us know in the discussion below.
Steve Jones
The Voice of the DBA Podcasts
The podcast feeds are now available at sqlservercentral.mevio.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.
or now on iTunes!
- Windows Media Podcast - 39.3MB WMV
- iPod Video Podcast - 29.1MB MP4
- MP3 Audio Podcast - 5.9MB
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.