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

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 🙂

Sean Pearce SSCoach Points: 15999 More actions · Answer 4

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]

steve.johnson 97721 Valued Member Points: 73 More actions · Answer 5

steve.johnson 97721

Valued Member

Points: 73

August 3, 2018 at 4:18 am

#2000128

Very useful - thanks for sharing!

Andy Steinke Mr or Mrs. 500 Points: 565 More actions · Answer 6

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.

Jeff Burton SSCommitted Points: 1551 More actions · Answer 7

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.

Lori Markle SSC Enthusiast Points: 185 More actions · Answer 8

Thank you John.

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

boldham Grasshopper Points: 11 More actions · Answer 9

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.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 10

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)