2

Modifying the Active Directory group membership for a particular user propagates to our SQL Server instances as expected — except when one specific group is added. What is causing this issue, and how can I fix it?

The servers in question are SQL Server 2016 SP1 running on Windows Server 2012 R2.

Given that the user is me, I can query my groups on one of the servers:

DROP TABLE IF EXISTS #Groups
CREATE TABLE #Groups
(
    [Name] NVARCHAR(30) NOT NULL PRIMARY KEY
)

INSERT #Groups ([Name]) VALUES
(N'ORG\DBDevelopers'),
(N'ORG\DBFullAccess'),
(N'ORG\DBReadOnly')

SELECT g.[Name], IS_MEMBER(g.[Name])
FROM   #Groups g

Results:

Name                           
------------------------------ -----------
ORG\DBDevelopers               1
ORG\DBFullAccess               0
ORG\DBReadOnly                 1

If my login is removed from or re-added to ORG\DBReadOnly, the change is propagated to the SQL Server instances as expected, modifying permissions accordingly. However, adding my login to ORG\DBFullAccess does not appear to modify permissions, nor does it change the results of the query above. However, the query:

EXEC xp_logininfo N'ORG\DBFullAccess', 'members'

says that I am a member of the group:

account name       type     privilege mapped login name  permission path
------------------ -------- --------- ------------------ ----------------
ORG\my.login       user     user      ORG\my.login       ORG\DBFullAccess
ORG\another.login  user     user      ORG\another.login  ORG\DBFullAccess
ORG\a.third.login  user     user      ORG\a.third.login  ORG\DBFullAccess

Logging onto the Windows server itself via RDP also indicates that I am a member of ORG\DBFullAccess. From a shell:

PS C:\Users\my.login> gpresult /R

<Banner, various likely-irrelevant information, etc.>

USER SETTINGS
--------------
    ⋮
    The user is a part of the following security groups
    ---------------------------------------------------
        ⋮
        DBFullAccess
        DBDevelopers
        DBReadOnly
        ⋮

This behavior is occurring with multiple SQL Server instances each running on their own Windows Server instances. Things we've tried include:

  1. Removing my login from DBFullAccess and re-adding it, forcing AD propagation after each step.
  2. Simply waiting overnight, with my machine powered off.
  3. Going directly onto one of the servers via RDP and using sqlcmd to query there. (I work remotely, and I connect to these servers by VPN, so my own PC is not itself on the AD network. I use SSMS via a runas /netonly command, which permits me to access the servers.)

Keep in mind, again, that adding and removing DBReadOnly works, and I can see the results when I disconnect from and reconnect to the SQL Server instance.

Our contract system administrator has recommended simply rebooting the Windows servers over the weekend. For a number of reasons, that action is suboptimal, plus we don't know for sure it will help.

How can I continue to troubleshoot the issue? Or, how can it be fixed?

Edit: We have a test server on AWS EC2 that is running SQL Server 2017 on Windows Server 2012 R2. The servers mentioned earlier are bare-metal servers in a colo facility. I do have the DBFullAccess group on the SQL 2017 instance.

  • This is a stab in the dark, but... does your account have Sysadmin permissions? If so, look in the other groups for an account named 'dbo'. – Laughing Vergil Jul 12 '18 at 23:30
  • This is what I am looking at: "Members of the sysadmin fixed server role enter every database as the dbo user. Checking permission for member of the sysadmin fixed server role, checks permissions for dbo, not the original login. Since dbo can't be added to a database role and doesn’t exist in Windows groups, dbo will always return 0 (or NULL if the role doesn't exist). " Some sort of ailiasing or something similar (including the shouldn't happen case of a windows account named dbo) is what I would be looking for. – Laughing Vergil Jul 12 '18 at 23:33
  • @LaughingVergil I shouldn't have the sysadmin role via any of my groups (a different group has it). Incidentally, the other two users in DBFullAccess would have that role. That at least gives us somewhere to look a bit, but it doesn't explain everything. – Andrew Jul 13 '18 at 0:00
  • Andrew - Let's get your AD Domain and Forest Functional Levels, & you might also confirm of these groups you list that you are testing with that are involved what are their Group Scopes... Can you confirm everything you mention is all setup in the same one domain, not trusted domains between different domains? – Pimp Juice IT Jul 13 '18 at 6:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.