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

  • SQLBlimp

    SSCertifiable

    Points: 6325

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

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8969

    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: 2422

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

    Jeff Bennett

    St. Louis, MO

  • SQLBlimp

    SSCertifiable

    Points: 6325

    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: 12808

    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: 6325

    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

    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: 6325

    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: 88659

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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