SQL Authentication Via AD Groups Part III: What About Orphaned Users?

Question

SQL Authentication Via AD Groups Part III: What About Orphaned Users?

SQLBlimp

SSCertifiable

Points: 6340
More actions
July 18, 2016 at 12:00 am

#315244

Comments posted to this topic are about the item SQL Authentication Via AD Groups Part III: What About Orphaned Users?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 1

A word of warning: if you have CONTAINED DATABASES, don't cleanup orphanded users (in fact all users in a contained database can be orphaned)

Wilfred
The best things in life are the simple things

chudman SSCrazy Points: 2453 More actions · Answer 2

Very nice discovery script and remediation script. I hope the PCI compliance auditors like it too.

Jeff Bennett

St. Louis, MO

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 3

Wilfred van Dijk (7/18/2016)
A word of warning: if you have CONTAINED DATABASES, don't cleanup orphanded users (in fact all users in a contained database can be orphaned)

Thanks for pointing this up. You are indeed correct.

Andrew G SSChampion Points: 12809 More actions · Answer 4

Thanks for the script John.

I noticed that @AcctName is not populated at any stage of the script, so the 0x534 logic will not work correctly.

Also, AD accounts and groups can be renamed, so a group may not have a matching name from AD, but the AD group SID still matches the SQL AD group name.

eg. GRP-ApplicationNameV001 is renamed to GRP-ApplicationNameV002 in AD, SQL still contains V001 and authentication continues to work. The script will detect this as an orphaned AD group and delete it.

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 5

Andrew G (7/19/2016)
Thanks for the script John.
I noticed that @AcctName is not populated at any stage of the script, so the 0x534 logic will not work correctly.
Also, AD accounts and groups can be renamed, so a group may not have a matching name from AD, but the AD group SID still matches the SQL AD group name.
eg. GRP-ApplicationNameV001 is renamed to GRP-ApplicationNameV002 in AD, SQL still contains V001 and authentication continues to work. The script will detect this as an orphaned AD group and delete it.

Hi - Let me look into this. Thanks.

ken.trock SSCertifiable Points: 5147 More actions · Answer 6

Thanks a lot for the series. We have an intranet application that allows us to add users to domain groups that we're either a trustee or admin of. I have DB roles in my SQL Server where these AD groups are members of. I'm pretty sure the "dsadd group" statements are executed right away in the web app. Yet when I add a user to a domain group this way there seems to be a lag between the time I do so and when they actually gain access. So I'm not exactly sure what's going on behind the scenes.

I think you said in one of the articles that when a user is removed from AD that their access to SQL Server is instantly gone (assuming they don't have access to a SQL login). Is that definitely the case?

Ken

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 7

ken.trock (7/19/2016)
...
I think you said in one of the articles that when a user is removed from AD that their access to SQL Server is instantly gone (assuming they don't have access to a SQL login). Is that definitely the case?

Hi - that is absolutely the case. They lose access when the AD account is deleted, disabled, expired or locked out.

Thanks

John.

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 8

If you setup a read-only replica in an Availability Group - you will also have orphaned users in the databases that are setup for read-only access. For windows users - you add them to the primary database without a login and on the secondary you add the login. When the user is mirrored to the read-only database it ties to the login and the user has access to that database.

For SQL logins - you have to create the user in the database with the SID from the login created on the replica.

Removing these orphaned users will remove access for the read-only users.

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