A Curious Case of Orphan User

Hi Friends, Today at work I received an access request to provide multiple database permissions to multiple AD groups on servers. After providing appropriate db roles membership to requested AD groups using TSQL code EXEC sp_addrolemember [<db role>],[<domain\ADGroup>], I marked the request complete. Sometime later I received mail reply from user that he is not able to logon to the server. This raised my curiousity as I was sure that I had completed the job very carefully. So I checked SQL Server log and found message similar to below:-

Date 11/29/2016 3:39:55 AM
Log SQL Server (Current - 11/27/2016 10:46:00 AM)
Source Logon
Message
Login failed for user 'Domain\User'. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.69.80]

Then, I realized that it may be an issue of Orphan User. User forgot to mention that AD Group logins are required to be created on server. So I ran below code to verify the same.

Above query did not return any result. This was a hint that there is some issue with AD Group login. To further verify, I used below codes:-

Even though this was enough to suggest that a login is missing for AD group, it can be tricky sometimes if we rely on single method. Like in my case exec sp_change_users_login @Action='Report' did not return any result which wrongly suggested me that Orphan user is not issue. Below is the complete code to create this scenario, and analyze the problem.

I hope this has been informative to all. Happy Coding 🙂

Please feel free to correct me by comments. Mention your approach if it differs from mine. If you like the article, do Like & Share.

Leave a Reply