One of my favorite new features for SQL 2008 is the ability to run commands against all your registered instances immediately. For me that saves time as I have over 150 instances (and growing). The other day I was reading Tim Ford’s blog entry about xp_logininfo which led to an exchange of emails between Tim and myself about what to do if your AD structure is such that you have nested groups. That exchange made me think about sp_validatelogins, a system procedure I came across when doing research for a project I put together last year.
So, I dug up the info on sp_validatelogins. The definition for this procedure in Books Online is:
“Reports information about Windows users and groups that are mapped to SQL Server principals but no longer exist in the Windows environment.”
Seems simple enough, right? So, if you have logins that have been removed from AD but still exist as a server login to your instance, you could use this procedure to help do some cleanup. I decided to run it against all 150 instances using the SSMS 2008 client and was shocked to have 109 rows returned. I am not sure how many rows I was expecting, but it was closer to zero than 109.
I dived into the results, sorting through a handful of AD groups that no longer exist in AD, and some old application service accounts that no longer exist, and then I see it, something that makes me stop and say WTF?
The SQL Service account, the one that is being used to run the instance, right now, this very moment, and is most definitely in AD, is listed as an orphaned login?
Um, yeah,…what? How can that be? Well, the system procedure uses the get_sid() function in order to match the SID of the login in AD to the SID of the login on the instance. If they do not match, then it returns that login as a row in the output. So, back to my original WTF? How is it that the SID’s do not match?
A little more digging turns up what could be two possibilities for this scenario. First, it could very well be the case that the login was dropped and recreated in AD, meaning there would be a new SID and thus a mismatch. I am certain that was not the case here. The other way I could see this happening has to do with the collation of the instance. I believe this is the issue I stumbled upon. So, in my case, the account
domain\user
did not match
domain\User
No, I don’t know why exactly. But when I dropped the login and manually did a search of AD to find the login and add it back in, the system procedure no longer returned the login as an orphan. There was one last thing to check, however, and that was to bounce the service. I did, and it did not come back up. So, I decided to go to the service configuration and also browse through AD to find the service account. Once I did I was able to get the service to start correctly.
so, use sp_validatelogins to find your orphaned users, but do not think that each login returned is not actually in AD. The output really only means there is a SID mismatch. Take the results and carefully decide what actions you want to take. While I originally thought this might be something worthwhile to automate, I am now thinking it would be best for me to manually review before allowing any automated cleanup process to take place.
And never trust everything you read in the BOL, they are meant as a guide only.
1 thought on “Find Orphaned AD Logins using sp_validatelogins”