October 17, 2019 at 2:59 pm
I currently have a scenario where an AD group that was defined as a SQL login with particular permissions got deleted from AD but not from SQL. So:
domaina\groupb - no longer exists as an AD group but still exists as a SQL login
It seems that members of the deleted group still have SQL access and all permissions by virtue of that membership. Of course I could delete the SQL logon, but is this expected behaviour? When, if ever, does SQL stop honouring the permissions of a deleted AD group and how does it even know who the members are/were?
With respect I am only interested in answers that explain this particular behaviour. I don't want to get into wider discussions about good security practice.
October 17, 2019 at 3:16 pm
to check whether a member of deleted group still has access to the SQL server, run the following:
exec xp_logininfo 'DOMAIN\user','all'
most probably, if it does have, then it should be via another AD groups on the server
to get group members of existing AD group, run the following:
exec xp_logininfo 'DOMAIN\Group','members'
moreover, it's possible to get list of AD groups in a domain:
EXEC xp_enumgroups 'DOMAIN'
October 18, 2019 at 11:29 am
Thanks but all the xp_logininfo queries told me was what I already knew:
exec xp_logininfo 'DOMAIN\user', 'all' - returned no rows (confirming that the user didn't have access via any other group)
exec xp_logininfo 'DOMAIN\Group', 'members' - couldn't find the group (confirming that the AD group didn't exist)
On the other hand querying sys.login_token did show the deleted group in the [name] column, showing that SQL was still honouring the permissions granted to the deleted group.
These results appear to be contradictory and this is the behaviour I am trying to understand.
October 18, 2019 at 3:30 pm
Are you sure the AD group was deleted from AD and not just renamed or moved? A way to check would be to use sys.server_principals to find the sid, and then search for the converted sid as an objectSid in active directory. There is an article here that explains the conversion of SQL Server sid to AD objectSid:
https://www.mssqltips.com/sqlservertip/3362/map-between-sql-server-sids-and-windows-sids/
Another possibility, are the users from a different domain that is trusted with the domain the server lives in? Sometimes xp_logininfo has difficulty determining membership of users in a foreign domain's group.
There may be other possibilities but those are the 2 I've come across before.
October 18, 2019 at 3:44 pm
Trusted domains don't come into this - we don't have any 🙂
If the group had been moved I'd have found it. I can't completely rule out the renamed suggestion but wouldn't such a group show up in the exec xp_logininfo 'DOMAIN\user', 'all' output? I will have a go at using the article to check - but probably not until next week 🙂
Thanks for your feedback.
November 5, 2019 at 3:48 pm
For those interested I have discovered that the group which I thought had been deleted had indeed been renamed. Probably the sid matching process pointed out by Chris would have got to it but in the end I found out by accident. Once again thanks to all for your interest.
November 5, 2019 at 11:45 pm
Glad you got it figured out. I know hunting through the Active Directory structures can be painful sometimes. 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply