In this article, I’ll discuss what SQL Server orphaned users and logins are, how they become orphaned in the first place, how to detect and prevent them from becoming orphaned and how to clean up orphaned users and logins. I’ll discuss what commands and options are available to handle orphaned users and logins along with some best practices to follow.
What are SQL Server orphaned users?
In simple terms, a user that exists in a database, without a corresponding login at server level (in the master database) is called an orphaned user. These are typically SQL authenticated logins. This definition doesn’t apply to contained databases, where the databases are self contained and authentication takes place without a login at server level.
What are SQL Server orphaned logins?
These are typically Windows accounts or Windows groups that exist as logins within the SQL Server, but the corresponding Windows accounts/groups no longer exist in the domain/Active Directory (AD)
What causes the database users to become orphaned?
As mentioned above, a database user with no corresponding login at server level in the master database is an orphaned user. Some of the most common causes for orphaned users include:
- Database restore from one server to another: When a database is backed up on one server and restored to a new server, you have to make sure all necessary logins are also copied over to the new server. If the corresponding logins are not already there on the new server, and also not copied over to the new server, then the user within the restored database becomes orphaned.
- Logins exist at server level, but with a non-matching SID: From the above point, even if you manage to script and copy the corresponding SQL authenticated logins to the new server, you must make sure the logins are scripted to include their SID. If the SID is not included in the login creation script, those logins get created with a new SID. As a result the SID of the database user doesn’t match the SID of the login, and the database user becomes orphaned. In this case, the orphan user fails to gain access into the database and receives an error (916) saying The server principal “LoginName” is not able to access the database “DatabaseName” under the current security context.
- Deletion of logins at server level: You decide that an SQL authenticated login is no longer needed, and you delete it at the server level – but that login has associated users in the databases hosted on that server. In this case, deletion of the login causes the corresponding database users to become orphaned.
- Incorrect login synchronisation between Always On Availability Group (AG) replicas: Imagine a scenario where you have two or more replicas in your AG. When setting up a new SQL authenticated login, you have to make sure the SIDs of the logins remain the same on all replicas – without that, the users become orphaned when the AG fails over to another replica and the server level login SIDs on that replica don’t match the database user SIDs.
What causes the Windows/AD accounts and groups to become orphaned?
A Windows user or group within an SQL Server database, with no corresponding login at server level (in the master database) is not strictly an orphaned Windows user/group That’s because SQL Server uses the Windows account/group’s SID to directly look up the AD regardless of a login being present at server level. However, if the Windows account or group is deleted within the domain/AD, but the deleted account/group is not removed from SQL Server (server as well as database level), then that Windows account/group becomes orphaned.
How to detect and resolve orphaned users in a database?
I have come up with an SQL Script that detects orphaned users for SQL authenticated logins, and also provides you the commands required to either delete those orphaned users, or resolve the orphaned users by either linking them to an already existing server level login or to create the missing login at server level.
To link the orphaned users with server level logins or to create the missing logins, the SIDs need to be scripted as strings. So, first create the function dbo.fn_hexadecimal_to_string in your DBA Tools or similar database. This is a one off requirement to create the function. The below example creates the function in MyScripts database:
USE MyScripts
GO
IF OBJECT_ID('dbo.fn_hexadecimal_to_string', 'FN') IS NOT NULL
BEGIN
RAISERROR('Function dbo.fn_hexadecimal_to_string already exists. Dropping and recreating', 0, 1)
DROP FUNCTION dbo.fn_hexadecimal_to_string
END
GO
CREATE FUNCTION dbo.fn_hexadecimal_to_string
(
@binvalue varbinary(256)
)
RETURNS nvarchar(514)
AS
BEGIN
--This code is originally created by Microsoft at:
--https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/security/transfer-logins-passwords-between-instances
--I have adapted it to create this function
DECLARE
@hexvalue nvarchar(514) = N'0x',
@i smallint = 1,
@length smallint = DATALENGTH(@binvalue),
@hexstring nchar(16) = N'0123456789ABCDEF',
@tempint smallint, @firstint smallint, @secondint smallint
WHILE (@i < = @length)
BEGIN
SET @tempint = CONVERT([smallint], SUBSTRING(@binvalue, @i, 1))
SET @firstint = FLOOR(@tempint / 16)
SET @secondint = @tempint - (@firstint * 16)
SET @hexvalue =
@hexvalue +
SUBSTRING(@hexstring, @firstint + 1, 1) +
SUBSTRING(@hexstring, @secondint + 1, 1)
SET @i = @i + 1
END
RETURN (@hexvalue)
END
GO
IF OBJECT_ID('dbo.fn_hexadecimal_to_string', 'FN') IS NOT NULL
BEGIN
RAISERROR('Function dbo.fn_hexadecimal_to_string has been created successfully', 0, 1)
END
ELSE
BEGIN
RAISERROR('Failed to create function dbo.fn_hexadecimal_to_string', 0, 1)
END
GONow use the below code to identify all orphaned SQL authenticated users in the current database. This code uses the function dbo.fn_hexadecimal_to_string, created using the script above
SELECT
dp.name AS DBUserName,
dp.sid AS DBUserSID,
sp.name AS ServerLoginName,
sp.sid AS ServerLoginSID,
'USE ' + QUOTENAME(DB_NAME()) + '; DROP USER ' + QUOTENAME(dp.name) + ';' AS ToRemoveOrphanedUser,
CASE
WHEN sp.name IS NULL
THEN 'CREATE LOGIN ' + QUOTENAME(dp.name) + ' WITH PASSWORD = ''ENTER A STRONG PASSWORD'', SID = ' + MyScripts.dbo.fn_hexadecimal_to_string(dp.sid) + ';'
WHEN (sp.name = dp.name) AND (dp.Sid <> COALESCE(sp.sid,0))
THEN 'USE ' + QUOTENAME(DB_NAME()) + '; ALTER USER ' + QUOTENAME(dp.name) + ' WITH LOGIN = ' + QUOTENAME(sp.name)
ELSE
''
END AS ToResolveOrphanedUser
FROM
sys.database_principals AS dp
LEFT JOIN
sys.server_principals AS sp
ON dp.SID = sp.SID OR dp.name = sp.name
WHERE
dp.type = 'S'
AND dp.authentication_type_desc = 'INSTANCE'
AND
(
dp.name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')
)
AND
(
dp.sid <> COALESCE(sp.sid,0)
)
GOAlong with highlighting the orphan users, the above code also outputs the necessary commands to either drop the orphaned user and to fix/resolve/remedy the orphaned user. Notice the last two columns of the output ToRemoveOrphanedUser and ToResolveOrphanedUser, which give you the necessary commands. Example below:
DBUserName DBUserSID ServerLoginName ServerLoginSID ToRemoveOrphanedUser ToResolveOrphanedUser
---------- ------------ --------------- --------------- ----------------------------------- ---------------------------------------------------------------------------------
USR1 0x32A80D8F26 NULL NULL USE [MyScripts]; DROP USER [USR1]; CREATE LOGIN [USR1] WITH PASSWORD = 'ENTER A STRONG PASSWORD', SID = 0x32A80D8F26
USR2 0x536754E4B0 USR2 0x4F0EBA880CB5C USE [MyScripts]; DROP USER [USR2]; USE [MyScripts]; ALTER USER [USR2] WITH LOGIN = [USR2]As you can see from the output, the orphaned users are resolved in one of the two methods. First one is to create the missing login at server level using the same SID as the user. Make sure you update the password place holder with a strong password. The second method is to update the SID of the orphaned user to match the SID of an existing server level login. Both methods use the ALTER LOGIN command. The same can be achieved using the system stored procedure sp_change_users_login using the auto_fix or update_one parameters – but sp_change_users_login has been deprecated and we should be using the ALTER LOGIN command instead.
See also: How to generate random and strong passwords for SQL Server logins
Similarly, even though the Windows authenticated users inside a database are not technically orphaned, the below script identifies those Windows accounts and groups that have no corresponding logins at server level. Notice the last two columns of the output ToRemoveOrphanedUser and ToResolveOrphanedUser, which give you the necessary commands:
SELECT
DB_NAME() AS DatabaseName,
dp.name AS OrphanUser,
dp.type,
'USE ' + QUOTENAME(DB_NAME()) + '; DROP USER ' + QUOTENAME(dp.name) + ';' AS ToRemoveOrphanedUser,
'CREATE LOGIN ' + QUOTENAME(dp.name) + ' FROM WINDOWS;' AS ToResolveOrphanedUser
FROM
sys.database_principals AS dp
WHERE
NOT EXISTS
(
SELECT
1
FROM
sys.server_principals AS sp
WHERE
dp.sid = sp.sid
)
AND
(
dp.type IN ('U', 'G')
)
GOHow to detect and remove orphaned Windows logins?
These are the Windows accounts and Windows groups that have been granted access to SQL Server at server level – but have since been removed from Windows domain or Active Directory.
The system stored procedure sp_validatelogins identifies such accounts. You simply have to run sp_validatelogins without any parameters. Any highlighted accounts need to be dropped using the DROP LOGIN command. Make sure you remove any corresponding mapped users from the databases as well.
Problems to be aware of when removing orphaned users:
While trying to remove an orphan user, you might run into a few common issues.
- The user owning a schema: If the orphan user has their own schema, the DROP USER command will fail. You first need to drop the schema using DROP SCHEMA command. Query the system catalog view sys.schemas to see which schemas are owned by the user.
- The user owning a role: If the orphaned user owns a database role, the DROP USER command will fail. You first need to either drop the role if its not required (using DROP ROLE command) or change the ownership of the role to another user, like dbo using the example command ALTER AUTHORIZATION ON ROLE::[AppReader] TO [dbo].
- The user owns an object: If the orphaned user owns an object like a table or stored procedure, either drop the owned object (if not needed) using the DROP command, or change the ownership to another user like dbo using ALTER AUTHORIZATION command
Best practices to follow to prevent orphaned users and logins:
- When you need to get rid of a login (be it Windows or SQL authenticated), don’t just drop the login at server level. First workout which of your databases have a mapped user for this login, drop the corresponding users in all those databases, and then finally drop the login. sp_helpuser can show you a list of all mapped database users. You can see the same from the login properties window in SQL Server Management Studio.
- Similar to the point above, if an AD account or AD group is removed from the Active Directory, remove that login from your SQL Server and databases. This helps keep your logins/users tidy. Have a periodic check in your diary or have a scheduled job that checks for any such logins by executing sp_validatelogins.
- When setting up logins on multiple replicas of an Always On Availability group, make sure you create all SQL authenticated logins using the same SID. To achieve this, first create the SQL authenticated logins on the primary replica. Then script those SQL authenticated logins along with their SIDs. Run that script on the rest of the replicas. This ensures that each SQL authenticated login has the same SID on all replicas. To script logins including the SID, download and use the stored procedure sp_help_revlogin from the Microsoft website.
- Use the same method as above when copying SQL authenticated logins between different SQL Servers, for example, from Dev or UAT SQL Server to production. This ensures the logins have the same SID in all environments, which prevents the users from becoming orphaned when databases are moved from one environment to another by way of backup/restore. Make sure you use different passwords though in different environments, as a security best practice.
- When you are about to go live with a new database server, make sure you check for orphaned users and logins prior to go live. This should be a part of your standard fit for production checks. As going live on a new server involves restore databases from different servers or domains, this is your first opportunity to clean out any orphaned users and logins.
I hope this article has covered all aspects of orphan user and login management. As always, happy to receive any feedback from you via the comments section below.
