2

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)

Improve this question
1
  • Being in multiple AD groups should not cause any problems. (Well, it can get messy if any of the groups have Deny rights, because those will override any Grant rights.) Can you provide more details?
    – Doug Deden
    Nov 12, 2019 at 23:51

1 Answer 1

Reset to default
2

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.

Improve this answer
3
  • I've checked the groups and they all appear. I removed the user account from SQL Logins so that only the groups show up, I then tried removing the user in AD from the group that has read access to the database and the extra privileges then work, but when I add her back to the AD group with read access she loses the additional privileges. The quick win would be to take the user out of the AD group that is causing the issue but that would mean she loses other access that AD group provides. But using xp_logininfo has made it easier to find the user, thanks HandyD
    – Drewbert
    Nov 13, 2019 at 22:52
  • Have you checked for any explicit DENY applied to the Read group? DENY overrides GRANT, so if the Read group is denying write access, then regardless of membership in the Write group, the user would not be able to perform Write activity, due to the DENY.
    – HandyD
    Nov 13, 2019 at 22:55
  • Yes I have checked that DENY has not been set anywhere, that was one of my first thoughts. I'm not a fan of using DENY for reasons just like this.
    – Drewbert
    Nov 14, 2019 at 2:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.