Dear Readers,
An attempt has been made to write a short script to find out SQL Server Orphan Logins. By the way, I am not sure if Microsoft has any term like “Orphan Login” within their documentation. However, I have seen that many people are talking about Orphan Logins but not orphan users. There is a difference between Orphan Login and Orphan users. In any case, lets call it “Orphan Login situation”
Orphan login situation –
A SQL Server Login may be an “orhpan” when there is no associated user/group at the AD Level/Server Level.
Orphan user –
User in the database is an “orphan” when there is no Login id associated with the user. This is true even if there is a login id that matches the user but there is no GUID (called a SID
) that matches.
How to simulate Orphan Login Situation?
First create Local user and a Local Group at windows server.
Login to your window server > server manager > Configuration > Local users and Groups
- Create one local user as servername\testorphanuser
- Create on local group as servername\testorphangroup
Login to your SQL server and create two logins for your windows user and group like below:
- servername\testorphanuser
- servername\testorphangroup
Once you are done – you can delete these local user and windows group at windows level.
Open server manager > configuration > local user and groups > right click user and delete it.
Open server manager > configuration > local user and groups > right click group and delete it.
Now, at SQL Server, you have two orphan logins.
So in order to find the orphan logins, you can use the below script.
This script is a modified version of my previous script – Who all have access to SQL Server via AD Groups?
/* Written by Sachin Diwakar for www.dbathings.com*/ CREATE TABLE #holdLogins ( accountname VARCHAR(max) ,type VARCHAR(20) ,priv VARCHAR(20) ,mappedlogin VARCHAR(max) ,ADGroup VARCHAR(max) ) SET QUOTED_IDENTIFIER OFF GO DECLARE @user_name VARCHAR(100) ,@exec_sql VARCHAR(2000) DECLARE user_cursor CURSOR FOR /*change here if you are including AD Groups as well*/ --SELECT name FROM sys.syslogins WHERE (isntname = 1) SELECT name FROM sys.syslogins WHERE (isntname = 1 AND isntuser = 1) AND ( NAME NOT LIKE 'NT SERVICE%' and name not like 'NT AUTHORITY%') OPEN user_cursor FETCH NEXT FROM user_cursor INTO @user_name WHILE @@FETCH_STATUS = 0 BEGIN SET @exec_sql = 'EXEC master..xp_logininfo @acctname = ''' + @user_name + '''' BEGIN TRY -- print @exec_sql INSERT INTO #holdLogins EXECUTE (@exec_sql) END TRY BEGIN CATCH INSERT INTO #holdLogins VALUES ( @user_name ,'--NA--' ,'--NA--' ,'--Seems Orphan Login--' ,@user_name ) END CATCH FETCH NEXT FROM user_cursor INTO @user_name END CLOSE user_cursor DEALLOCATE user_cursor SELECT * FROM #holdLogins DROP TABLE #holdLogins
I tried this script with couple of scenarios only. However, I would encourage if you can comment here and share your feedback if this works for you. I hope this helps..