Post reply

SQL Authentication Via AD Groups Part II: Who has what access?

  • February 18, 2016 at 8:12 pm

    #1859720

    Great article/script and VERY eye-opening!! I'm not part of our company's DBA team, but I have a few sandboxes to play in. Just running it in our test environment makes me fearful of what production would reveal! Although, our team is sharp and in all likelihood, old accounts have been disabled.

    Thank you Joe for the add-on column 'is_disabled'. Folks, don't forget to add the new column to your final select statement.

    I was unable to initially run this on a 2005 sp4 instance, due to variable declaration errors. So I had to change the following

    from:

    declare @ctr nvarchar(max) = '', @AcctName sysname = ''

    to:

    declare @AcctName nvarchar(128)

    declare @ctr nvarchar(max)

    after that, it worked great! Thanks again! I will pass this on to my fellow DBA wannabes and to the DBA team.

  • March 9, 2016 at 4:32 pm

    #1864128

    Great security info.

  • July 21, 2016 at 12:10 pm

    #1891419

    This is great, thank you!!!

  • August 3, 2018 at 1:54 am

    #2000120

    Thanks for sharing. I tend to be on KenpoDBA's side though... don't like xp_logininfo either.
    Btw, did a little test: created a plain login and granted it CONTROL SERVER. It was very happy to suddenly get access to all databases without being noticed by your script 🙂

  • August 3, 2018 at 3:30 am

    #2000126

    This is very good info to have because it can bite you sometimes.

    True story: Working at a top insurance company I discovered that an I.T. admin group had been granted sysadmin on production. Within that group was another group and within that was another. I kid you not, a member of that group was [Domain Users].

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • August 3, 2018 at 4:18 am

    #2000128

    Very useful - thanks for sharing!

  • August 3, 2018 at 4:21 am

    #2000129

    Fantastic reminder about this.  I don't have the luxury of getting to sit in my ivory tower and say "that's not my problem" in my environment when others make questionable decisions.  This is a fantastic little script to demonstrate the pitfalls of poorly managed AD groups being used to access SQL resources.

  • August 3, 2018 at 5:35 am

    #2000143

    Great article!  xp_logininfo is invaluable but you have to know it's limitations:
    - returns empty row when AD account exists but it does not have login authentication in the SQL instance
    - returns error 0x534 when the AD account does not exist
    - when used with the members parameter only lists the 1st level of members and does not list members of all subgroups
    - returns an error with the members parameter for a group containing mixed AD domain members
    - for logins with group authentication who are in a lower level subgroup of a group login the permission path is the highest level group
    In my organization I'm required to disable SQL instance access after X days inactivity for certain classes of logins/users this year which was made so much harder to do since SQL does not have LastLoginDateTime for server principals.  I implemented a login trigger to record the last login date/time and used that data to determine when to disable the inactive logins.  The hardest part of the login trigger is dealing with the rare/random error with login failed to due to trigger execution for a login that is connecting too repeatedly/rapidly for the trigger to handle.  Users with "group authentication" are hard to find completely with xp_logininfo so I load tables with all AD accounts (one for each AD domain) and then test/probe each AD account with xp_logininfo for db instance access which takes awhile to probe 5K+ logins in 100+ db instances. To disable the group-authenticated individuals I have to create the login and then disable it but of course when I do that I lose the permission path from xp_logininfo to the original group.  I have another trigger to log the activity of drop/alter/create LOGIN statements to a table.  The code for this operation is complex.  I'm still working out the bugs in it after implementing it in May and disabling logins once a week.  A large percentage of the login access was disabled due to no login activity recorded.  I think most users are unaware of all the SQL access they have from groups they are members of owned by others in different divisions.

  • August 3, 2018 at 11:32 am

    #2000203

    Thank you John.

    I JUST added your script from 2016 to my admin database yesterday. It has already helped me.

  • August 3, 2018 at 2:34 pm

    #2000229

    Hi, can you comment on whether it is possible to run SQL and Agent as a user from a trusted domain? Thanks for any feedback.

  • August 5, 2018 at 9:09 pm

    #2000356

    KenpoDBA - Thursday, February 18, 2016 7:11 AM

    Yeah, I make a product that does this and also expands all the subgroups as well to give you not only a flat list, but also the full group chain from AD.However, I go outside of SQL to do it and the confines of this article are to keep within SQL itself.I've never been happy with xp_logininfo. I think it's a half-assed attempt to give us real info. A lot of things they write are like that though. They stop short of giving us what we actually need.

    Missed this... what's the link for your product that does this?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 16 through 25 (of 25 total)

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