Fix orphaned SQL Server users after a restore (Msg 15023 and Msg 916)
By Scott Coffey, Senior Database Developer. Updated 2026-10-20.
You restore a production database to a test or replacement server. The application login exists on the new server, but the app cannot connect, and when you try to map the login to the database in SSMS you get:
Msg 15023, Level 16, State 1
User, group, or role 'appuser' already exists in the current database.Connecting with the database as the default or initial catalog fails with Msg 4060, "Cannot open database requested by the login", and running USE Sales; as that login returns:
Msg 916, Level 14
The server principal "appuser" is not able to access the database "Sales" under the current security context.The user is orphaned. Nothing is wrong with the password or the permissions inside the database; the link between the login and the user is broken.
Why it happens
A database user is linked to a server login by security identifier (SID), not by name. When you create a SQL login with CREATE LOGIN appuser WITH PASSWORD = ..., SQL Server generates a new SID for it. The same name created on two servers gets two different SIDs. The database carries the user and the old SID with it, so after a restore to another server the user points at a SID that no login on that server has.
Windows logins behave differently. Their SIDs come from Active Directory, so DOMAIN\svc_app has the same SID everywhere. A Windows user is only orphaned when the login has not been created on the new server, and CREATE LOGIN [DOMAIN\svc_app] FROM WINDOWS; fixes it with no remapping. Contained database users have no login at all and are never orphaned.
Step 1: find the orphaned SQL users
Run this in the restored database. It needs VIEW DEFINITION on the database and changes nothing.
SELECT dp.name AS user_name,
dp.sid AS user_sid,
login_by_name.name AS same_named_login,
login_by_name.sid AS same_named_login_sid
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
ON sp.sid = dp.sid
LEFT JOIN sys.server_principals AS login_by_name
ON login_by_name.name = dp.name AND login_by_name.type = 'S'
WHERE dp.type = 'S' -- SQL user
AND dp.authentication_type = 1 -- mapped to an instance login
AND dp.name NOT IN (N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')
AND sp.sid IS NULL -- no login has this SID
ORDER BY dp.name;Filtering on authentication_type = 1 matters. Users created WITHOUT LOGIN (often used for module signing or EXECUTE AS) also have no matching login, by design, and you should not remap them. If same_named_login is filled in, the login exists with a different SID and step 2 applies. If it is empty, go to step 3.
Step 2: remap to an existing login
Use ALTER USER ... WITH LOGIN. It rewrites the user's SID to match the login and keeps the user's schema, role memberships, and permissions.
USE Sales;
ALTER USER [appuser] WITH LOGIN = [appuser];You will still see sp_change_users_login 'Auto_Fix' in older answers. Microsoft has marked it deprecated, it does not handle Windows users, and its auto-fix mode can create a login with a password you pass on the command line. ALTER USER is the supported replacement.
For several users, build the statements from the step 1 query and review them before running anything. The pattern in the SQL Rig script defaults to a dry run and remaps only when a login with the same name exists:
SET NOCOUNT ON;
DECLARE @DryRun bit = 1, @sql nvarchar(max) = N'';
SELECT @sql += N'ALTER USER ' + QUOTENAME(dp.name)
+ N' WITH LOGIN = ' + QUOTENAME(dp.name) + N';' + CHAR(10)
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON sp.sid = dp.sid
JOIN sys.server_principals AS l ON l.name = dp.name AND l.type = 'S'
WHERE dp.type = 'S' AND dp.authentication_type = 1
AND dp.name NOT IN (N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')
AND sp.sid IS NULL;
IF @DryRun = 1
PRINT @sql; -- review first
ELSE
EXEC sys.sp_executesql @sql;Step 3: create a missing login with the database's SID
When no login exists yet, create it with the SID already stored in the database. The user then maps on its own, and the next restore of this database to this server will not orphan it again.
USE Sales;
SELECT name, CONVERT(varchar(100), sid, 1) AS sid_literal
FROM sys.database_principals
WHERE name = N'appuser';
-- Paste the sid_literal value; supply the password at run time, never in source control.
CREATE LOGIN [appuser]
WITH PASSWORD = N'<set at run time>',
SID = 0x0123456789ABCDEF0123456789ABCDEF, -- example value
CHECK_POLICY = ON;CREATE LOGIN accepts the SID only as a literal, so copy the value from the query output rather than trying to pass a variable.
Step 4: stop it happening on the next restore
The lasting fix is to create SQL logins on every server with the same SID and password hash as the source. On the source server, generate the statements from sys.sql_logins:
SELECT N'CREATE LOGIN ' + QUOTENAME(name)
+ N' WITH PASSWORD = ' + CONVERT(nvarchar(max), password_hash, 1) + N' HASHED'
+ N', SID = ' + CONVERT(nvarchar(100), sid, 1)
+ N', CHECK_POLICY = ' + CASE is_policy_checked WHEN 1 THEN N'ON' ELSE N'OFF' END
+ N';' AS create_login_stmt
FROM sys.sql_logins
WHERE name NOT LIKE N'##%' AND name <> N'sa';Run the output on the target server before the first restore. The query reads password hashes, so it needs CONTROL SERVER or sysadmin, and the output should be handled like a credential: keep it out of email, tickets, and source control. Microsoft's sp_help_revlogin procedure does the same job and also carries default database and language.
Check the fix
Rerun the step 1 query and confirm it returns no rows, then connect as the application login and run a simple read. If you restore databases on a schedule, for example a nightly refresh of a reporting copy, add the step 1 query as a final SQL Server Agent job step that fails the job when it finds rows, so an orphaned user shows up as a failed job instead of a broken report the next morning.