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.
|
1 2 3 4 |
" ]-- Check if user exists for [domain\ADGroup] EXEC master..xp_logininfo @acctname = 'Domain\ADGroup', @option = 'members' |
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:-
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Check if ADGroup Login exists use master go select * from sys.server_principals sp where name = 'domain\ADGroup' go -- Verify user for ADGroup use AdventureWorks2014 go select * from sys.database_principals sp where name = 'domain\ADGroup' go exec sp_change_users_login @Action='Report' GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
-- Create login use master go create login [domain\ADGroup] from windows go -- Create User use AdventureWorks2014 go create user [domain\ADGroup] for login [domain\ADGroup] go -- Drop login use master go drop login [domain\ADGroup] go -- Verify login is deleted use master go select * from sys.server_principals sp where name = 'domain\ADGroup' go -- Verify orphan user use AdventureWorks2014 go select * from sys.database_principals sp where name = 'domain\ADGroup' go exec sp_change_users_login @Action='Report' GO /* Even though the login does not exists, still we are permitted to provide permissions to orphan user */ use AdventureWorks2014 go EXEC sp_addrolemember [db_datareader],[domain\ADGroup] GO |
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.
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.