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

  • SQLBlimp

    SSCertifiable

    Points: 6322

    Comments posted to this topic are about the item SQL Authentication Via AD Groups Part II: Who has what access?

  • Joe O'Connor

    SSC Eights!

    Points: 904

    Wow! Thank you for this! As the "New" DBA at my company (aka the only one), noone has any idea what most databases are for or who uses them, and security auditing was on my list. After a couple test runs on some non-production databases, I'm not dreading it nearly as much as I was

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    This method doesn't handle nested AD groups does it?

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • Andy Warren

    SSC Guru

    Points: 119676

    I don't spend much time worrying about people in wrong groups other than any granted sysadmin or similar. If the company has groups set up so that managing them can be offloaded to someone outside the DBA team I'm going to trust them to get it right - I'm not the reviewer, approver, or safety net. Tough love! For all that it's interesting to see a daily report of changes that affect db access.

    Where something like this helps is figuring out if/why/how they have access, or how to grant them the access. I typically have some "DB-" groups where I can manage access directly, but often those have nested groups (typically team groups) so that when a team member is added they get access automatically. It can in some environments be a pain to figure out the right place to add them.

    Good follow up article.

  • SQLBlimp

    SSCertifiable

    Points: 6322

    KenpoDBA (2/18/2016)


    This method doesn't handle nested AD groups does it?

    It does show anyone whose access comes in via a nested group. Are you looking for the entire group path back to the login?

    If yes then not. I don't know if SQL server can resolve that.

    Thanks

    John.

  • SQLKnowItAll

    SSC Guru

    Points: 61781

    Great method! I prefer not to use sp_msforeachdb as it can not always produce the right results. I don't remember the conditions, but I have had issues with it in the past in terms of it not hitting every database or even deadlocking. I prefer to use a cursor that can loop through databases that are in the proper state. Also, since sp_msforeachdb is undocumented, who knows what changes they may make to it in the future.

    Thanks,
    Jared
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    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.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • Andrew-H

    SSC Veteran

    Points: 213

    Excellent! Thank you for this handy tool.

  • Joe O'Connor

    SSC Eights!

    Points: 904

    I did some more experimenting with this, and noted one omission that might be useful - including "is_disabled' in the output. As it doesn't appear to come back when bringing in the Windows users, it might have to be a separate step in the job to update the temp table - like right after the Server_Roles section. I'll be trying to add it myself when I get a chance

  • SQLBlimp

    SSCertifiable

    Points: 6322

    Joe O'Connor (2/18/2016)


    I did some more experimenting with this, and noted one omission that might be useful - including "is_disabled' in the output. As it doesn't appear to come back when bringing in the Windows users, it might have to be a separate step in the job to update the temp table - like right after the Server_Roles section. I'll be trying to add it myself when I get a chance

    Great idea! I will try to figure out how to do it here as well. the one thing that I see that might make it problematic is when the windows login is through a group; we would only report on the disabled condition of the group, and not of the windows account itself.

    Thanks

    John.

  • Jeff_C

    Old Hand

    Points: 317

    Hi John,

    Great script. I just noticed though that the final output does not show deleted windows users. If you add a coalesce statement as below I think that fixes it

    Select

    DISTINCT

    LL.[Account Name]

    ,@@SERVERNAME as [Database Server]

    ,UP.dbname as [Database Name]

    ,coalesce(LoginType,LL.type) as LoginType

    --,LL.Privilege

    ,LL.Server_Roles

    ,LL.[Permission Path]

    ,UP.Permissions_user as [User Privileges]

    from #LoginsList LL

    left join #UserPermissions UP

    on LL.[Permission Path] = UP.UserName

    -- Comment out the where clause to see all logins that have no database users

    -- and their server roles.

    -- where exists(select 1 from #LoginsList U2 where U2.[Account Name] = UP.[UserName])

    order by

    LL.[Account Name]

    ,UP.DBName;

    Regards

    Jeff.

  • Joe O'Connor

    SSC Eights!

    Points: 904

    SQLBlimp (2/18/2016)


    Joe O'Connor (2/18/2016)


    I did some more experimenting with this, and noted one omission that might be useful - including "is_disabled' in the output. As it doesn't appear to come back when bringing in the Windows users, it might have to be a separate step in the job to update the temp table - like right after the Server_Roles section. I'll be trying to add it myself when I get a chance

    Great idea! I will try to figure out how to do it here as well. the one thing that I see that might make it problematic is when the windows login is through a group; we would only report on the disabled condition of the group, and not of the windows account itself.

    Thanks

    John.

    I figured it out. I ran through the list twice, once for the users, and once to cascade the groups down to the users - which isn't terribly important, you cannot actually disable a windows group within SQL (learned that through this experiment).

    Snippet of code to add it is:

    -- Get is_disabled into the mix

    -- Add column to table

    alter table #LoginsList add [is_disabled] int;

    update LL

    set

    is_disabled = p.is_disabled

    FROM

    #LoginsList LL

    INNER JOIN (Select name, is_disabled FROM sys.server_principals) p

    ON LL.[Account Name] = p.name

    -- cascade the permission path values down to the users granted access from that path

    update LL

    SET is_disabled = pp.is_disabled

    FROM

    #LoginsList LL

    INNER JOIN (select L2.[Account Name], L2.is_disabled FROM #LoginsList L2) pp

    ON LL.[Permission Path] = pp.[Account Name]

    then just add the column to the output at the end

  • SQLBlimp

    SSCertifiable

    Points: 6322

    Joe: Thanks! I truly appreciate it!

    John.

  • Jason Rendon

    Grasshopper

    Points: 17

    Thank you as well, this helped out a lot!!!!

  • JakDBA

    SSCommitted

    Points: 1610

    This is well fabricated one, I am doing something very similar thing RBAC remediation and I am stuck at place where user defined roles got what access , I mean we are moving something from standard roles like db_owner to XXXX_db_owner and the this role should have all or few of the access grants from parent role got. and stuck with get the access grants for these used defined roles , cause when we moved few users are losing their access to certain things. so is there any way or script any one can help me with which can say exactly.

    @JayMunnangi

Viewing 15 posts - 1 through 15 (of 26 total)

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