asked
How does SQL Server handle permissions for a user in multiple AD groups with differing permissions
In an Active Directory setting, if a user exists in multiple groups with permissions in a database, does the user get the greatest permissions allowed, or the least, if the groups have differing permission levels. For example, if a user is a member of MyDBUsers with read/write permission and also MyDBAdmins with dbo permissions, does the user have dbo privilege?
If it's all GRANTs, they will get the union of all permissions for the the groups they belong to. For example, if one group is a member of the db_datareader role and the other group is a member of the db_datawriter role, a member of both groups will be able to read and write. I believe a DENY will trump a GRANT in most cases. For example, you could be in a select group that is a member of db_datareader but also in a group that contains everyone except HR that is denied permissions on the EmployeeSalary table, in which case you could read anything except that table. I think there are some roles who can't be denied certain things though. I doubt you can deny anything to a memver of the sysadmin server role, or anything at database level to a member of dbowner.
Thanks, David! That's what I thought but I wanted to know for sure.
18 People are following this question.
