May 6, 2020 at 5:22 pm
I have a strange problem regarding an Active Directory group that has access to a database.
We have a AD group called APP_OP that was created a long time ago to give users access to specific databases used by an in-house application. So basically we created the group as a login on the server and mapped it to the various databases. Pretty standard stuff.
At some point in the past - this would have been years ago - the group was un-mapped from most of these databases.
My issue is that on more than one of these databases, users in that AD group still have the access that was granted. I've verified that if I remove a user from the group, they lose the access; and if I add a user to the group, they gain access.
When I looked in the graphical tree for users on the database, the group appeared with a little red X which I took to mean it's no longer active. But even after deleting it, the members of it still have the access...
I have to admit I am at a loss. How can an AD group be granting access to a database where it is no longer mapped, and where it no longer exists? Could SQL be caching this somehow?
I've confirmed that the group doesn't have access to databases where it was never mapped in the first place, so it doesn't appear to be any kind of server-wide thing.
Any ideas or suggestions would be greatly appreciated.
May 6, 2020 at 5:38 pm
What if these users are in another AD groups that have access to the databases?
You would have to dig deeper, you might have different groups and these users may be in these groups.
not only at the database level, you'd have to dig AD groups at the instance levels.
You can use this command to see members inside an AD Group in SQL Server.
May 11, 2020 at 2:27 pm
Thanks for the reply...
I have been digging around for other AD groups but it really does seem to be specific to this one. If I remove a user from the group, they lose the additional access to the database; if I add a user to the group, they gain access.
May 11, 2020 at 5:25 pm
Is the AD group still setup as a login to the server? If so - then it doesn't matter if the group is enabled or disabled, in fact - that has no impact on AD groups.
If the group is included as a User in the database - again, enabled/disabled has no impact on permissions. You have to remove the group from the database to remove permissions for the group...but that will not necessarily be enough. There are some cases where SQL Server will add the user from a group as a user in the database - and the user will inherit those permissions if they get access to the database through a different group.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 11, 2020 at 5:29 pm
It still could be an issue with nested groups or something along those lines. Try checking the user_token DMV as that will pick up nested groups. So you add an account to that group and they gain access to Whatever database then check with:
USE WhateverDatabase
go
EXECUTE AS LOGIN = 'YourDomain\YourUser'
go
SELECT * FROM sys.user_token
go
REVERT
Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply