asked
SQL Database Roles versus Active Directory Groups
Forgive me if this question has been asked before, but I failed to find anything. I'm trying to determine what the pros and cons of using Database Roles versus Active Directory Groups. I know this is not a true dichotomy, because one can add AD Groups/Users to Database Roles, but my concern is flexibility. With database roles, I can create the same role for each database, but add different users per database. For example, I can create a Db_ReadOnly role in Db1 and add AD users John Smith and Mary Kay. Then I can create the same role in Db2, but add a different set of AD users: Bob Anderson and Sue Johnson. However, if I were using AD Groups, I'd have to create different ones per database. I think it would be easier to add the users to Database Roles as opposed to creating different AD Groups. In an environment like mine with multiple databases (each with different security needs) the less groups to create the better. Please opine. What do you think is the better approach? Are there any drawbacks to using Database Roles only? Thanks for your time.
I would normally use both. Database roles for the types of thing that need to be undertaken in the database, AD groups to assign users to them, again based on job need. A factor here is that AD groups are often maintained outside of the DB sphere - this may be a pro or a con depending on your viewpoint, but usually means that when people change jobs, their permissions can be easily updated by virtue of group memberships. Database roles travel with the database. So in TEST and DEV environments, I want one set of users to have a given role, but it's probably very different in PROD environments - better to keep the database the same (including roles) and simply assign permissions to the role by giving users membership of an AD group. Database roles can also be source controlled - so you can ensure that permissions can be tested, and approved, with the application - leaving only who has those permissions as the variable factor. AD groups are a convenient way of giving those roles to a set of people based on a job need - for example "Salespeople" need the ability to write to the [SalesOrders] table, but not the [Salaries] table, however the people that make up "SalesPeople" will change from Dev to PROD, and for one region to another (if I have separate applications). So create SalesPeople as a databaserole, and assign permissions to an AD group (or groups) per server, then assign users to the groups. As the users' role in the business changes, move the user in or out of the AD groups, and the rest of the permissions follow. What I particularly like about this approach is that applications can read AD groups, so for instance I can get the web server delivering the application to check that the user is a member of that AD group too. my 2p.
Thank you very much, Dave. There is a lot of good information here that will help in my decision. Thanks, again.
15 People are following this question.
