Sql-server – User is in multiple Active Directory groups with multiple roles in database

permissionsrolesql serversql-server-2012

I have a user that is a member of multiple Active Directory (AD) groups. Within SQL Server 2012, these AD groups have been assigned to different database roles. This user is not getting the correct privileges unless I add the AD user directly to the role (they are still a member of the groups as well). Is there an issue if a person is a member of different roles or would this be an AD issue when adding to SQL Server?

Preferably I would like only AD groups and not individual AD users within the databases.

One user in-particular is in a db role that has read access to a group of tables, another role that allows read write access to all tables. Both roles are based on AD groups she needs to be a member of. But because she is in both roles and AD groups, it is not allowing read write access unless her AD user is added to the read write role. (the AD group won't work)

Best Answer

First step is to validate that the user is seen as a member of both AD groups by SQL Server by running the below as a sysadmin:

EXEC xp_logininfo 'DOMAIN\UserName', 'all';

The returned table should show all paths of server access for this user, i.e. one row for each AD group the user is a member of that provides access to SQL Server. If the group does not appear, verify in AD that they are in fact a member.

Next, have the user log out and back in from the machine from which they're trying to access SQL Server. Their AD Group membership is contained in a security token issued by the DC when they log in. If they haven't logged out and back in since being added to the AD Group then the security token will not reflect their current membership status and so the write access won't be granted.

Related Solutions

Sql-server – User in multiple windows groups mapped to sql logins

If I am understanding your question correctly, then yes the user will be able to run both applications with database connection issues.

If User1 is part of Windows Groups Group1 and Group2, and Group1 is mapped as a SQL Server login, as well as Group2 is mapped to a different SQL Server login. ~~And if Group1 is mapped to a database user in Database1 and Group2 is mapped as a database user in Database2, then User1 should have access to both Database1 and Database2.~~ And if Group1 and Group2 are both mapped to TheOnlyDatabase, then the user will have the accumulations of both database users' permissions.

This will return only one of the database users (as expected):

select user_name() as database_user_name

This will return 1 for both queries if the users is a member of both Windows Groups:

select is_member('Domain\Group1') as member_of_group_1

select is_member('Domain\Group2') as member_of_group_2

Sql-server – SysAdmin server role and Active Directory Groups

Individual accounts are going to be much harder to administer. They give you more granular control over access, but you can not keep out your Active Directory administrators. Anyone who is granted local administrator rights on your server can gain sysadmin access to the instance. And, as a very good DBA told me once, if you can't trust your domain admins you've got bigger problems on your hands.

By abstracting your access to AD groups, you gain two benefits:

Easier to manage access groups and rights based on roles, combined with stratified access control for other assets using those AD groups.
Provide more consistent auditing, as you will only need to show access by AD group and then who is contained within that group.

Best Answer

Related Solutions

Sql-server – User in multiple windows groups mapped to sql logins

Sql-server – SysAdmin server role and Active Directory Groups

Related Question