What are Orphaned Users
“Orphaned Users” is a common issue in SQL Server where a Database User is no longer associated with its relevant Server Login.
This often happens when the Server Login is deleted (even if it’s recreated later), or when the database is moved or restored to a different SQL Server. You can find some more info on it in this article from Microsoft.
The symptoms that you can expect from such a scenario is inability to use such database users. Specifically, using the login to connect to the database that it’s supposed to be mapped to. It just doesn’t work. You’ll receive the following error:
Login failed for user ‘MyUser’. (Microsoft SQL Server, Error: 18456)
The Login could exist on the server level. The User also exists within the relevant database. But it doesn’t work because the SID that the database user is mapped to, is different from the SID of the actual Server Login.
This is the real problem, because Users are mapped to their Logins by their SID, not by their names. And every time you create a new Login, it receives a new, random SID.
The “Correct” Solution
The most correct solution for this problem, is to have consistent SIDs to your Logins across all your SQL Servers. So that even when a database is moved to a different server, it could still use the same SID that it was originally created for. And also, when you recreate a previously deleted Login, you’d need to create it with the same SID that it originally had.
This is, obviously, not a trivial matter, and not always possible.
But if this is a direction that interests you, then you will find the following very useful: A long time ago, Microsoft created a special procedure for the purpose of migrating logins from one server to another. This procedure outputs the CREATE script for a specified login (or all logins), by retaining its original SID and even its password (if it’s a SQL Login). They called it “sp_help_revlogin” and published this article to explain how to use it. Even though this is very, very, very old, they still only provide it till this day as a “downloadable” script instead of a built-in system procedure.
One drawback of this procedure, though, is that it only provides the creation script for the Login itself, but not for its permissions, roles, etc.
If you ever need to migrate Logins from one SQL Server to another, including their roles and permissions and such, I recommend the easy-to-use Powershell library “dbatools” which contains the command Copy-DbaLogin. More info here.
The Easy Solution
Orphaned Users are nothing new in SQL Server. That’s why the (now deprecated) system procedure sp_change_users_login exists since, about, forever. You can use it with parameter @Action = ‘Auto_Fix’ and it’ll automatically remap an orphan database user to a Login with the same name, if such exists.
But this procedure (and even its modern counterpart ALTER USER) only affects a single Login at a time.
So, what if you have A LOT of such orphaned users? Even worse, what if you have a lot of servers and a lot of orphaned users in each? What if this is a commonly recurring problem within your organization?
Will you write the same command every time for every user?
I wouldn’t.
I would prefer to write a T-SQL script that will do it for me.
Kinda like the following script, which can be used to reconnect all users within the current database:
Transact-SQL
SET NOCOUNT ON; DECLARE @user NVARCHAR(MAX); DECLARE Orphans CURSOR FOR SELECT dp.name AS user_name FROM sys.database_principals AS dp LEFT JOIN sys.server_principals AS sp ON dp.SID = sp.SID WHERE sp.SID IS NULL AND authentication_type_desc = 'INSTANCE' AND dp.name IN (SELECT name FROM sys.server_principals); OPEN Orphans FETCH NEXT FROM Orphans INTO @user WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @Command NVARCHAR(MAX); SET @Command = N'ALTER USER ' + QUOTENAME(@user) + N' WITH LOGIN = ' + QUOTENAME(@user) PRINT @Command EXEC (@Command); FETCH NEXT FROM Orphans INTO @user END CLOSE Orphans DEALLOCATE Orphans
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
SET NOCOUNT ON;
DECLARE @user NVARCHAR(MAX);
DECLARE Orphans CURSOR FOR
SELECT dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.SID = sp.SID
WHERE sp.SID IS NULL
AND authentication_type_desc = 'INSTANCE'
AND dp.name IN (SELECT name FROM sys.server_principals);
OPEN Orphans
FETCH NEXT FROM Orphans INTO @user
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Command NVARCHAR(MAX);
SET @Command = N'ALTER USER ' + QUOTENAME(@user) + N' WITH LOGIN = ' + QUOTENAME(@user)
PRINT @Command
EXEC (@Command);
FETCH NEXT FROM Orphans INTO @user
END
CLOSE Orphans
DEALLOCATE Orphans
Or like this script, which also can reconnect all users to their logins, but for ALL of the databases on the server:
Transact-SQL
SET NOCOUNT ON; DECLARE @db SYSNAME, @user NVARCHAR(MAX); IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp; CREATE TABLE #tmp (DBName SYSNAME NULL, UserName NVARCHAR(MAX)); exec sp_MsforEachDB ' INSERT INTO #tmp SELECT ''?'', dp.name AS user_name FROM [?].sys.database_principals AS dp LEFT JOIN sys.server_principals AS sp ON dp.SID = sp.SID WHERE sp.SID IS NULL AND authentication_type_desc = ''INSTANCE'' AND dp.name IN (SELECT name COLLATE database_default FROM sys.server_principals);' DECLARE Orphans CURSOR FOR SELECT DBName, UserName FROM #tmp; OPEN Orphans FETCH NEXT FROM Orphans INTO @db, @user WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @Command NVARCHAR(MAX) SET @Command = N'USE ' + QUOTENAME(@db) + N'; ALTER USER ' + QUOTENAME(@user) + N' WITH LOGIN = ' + QUOTENAME(@user) PRINT @Command; EXEC (@Command); FETCH NEXT FROM Orphans INTO @db, @user END CLOSE Orphans DEALLOCATE Orphans
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
SET NOCOUNT ON;
DECLARE @db SYSNAME, @user NVARCHAR(MAX);
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
CREATE TABLE #tmp (DBName SYSNAME NULL, UserName NVARCHAR(MAX));
exec sp_MsforEachDB '
INSERT INTO #tmp
SELECT ''?'', dp.name AS user_name
FROM [?].sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.SID = sp.SID
WHERE sp.SID IS NULL
AND authentication_type_desc = ''INSTANCE''
AND dp.name IN (SELECT name COLLATE database_default FROM sys.server_principals);'
DECLARE Orphans CURSOR FOR
SELECT DBName, UserName FROM #tmp;
OPEN Orphans
FETCH NEXT FROM Orphans INTO @db, @user
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Command NVARCHAR(MAX)
SET @Command = N'USE ' + QUOTENAME(@db) + N'; ALTER USER ' + QUOTENAME(@user) + N' WITH LOGIN = ' + QUOTENAME(@user)
PRINT @Command;
EXEC (@Command);
FETCH NEXT FROM Orphans INTO @db, @user
END
CLOSE Orphans
DEALLOCATE Orphans
Feel free to use the above scripts for your convenience!