A Curious Case of Orphan User

Spread the love

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 [],[], 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.

--	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:-

--	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.

--	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.

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.