Orphaned users

Servers have Logins, and databases have Users.

A Login and a User account are linked, sharing the same name and the same SID. Naturally, for each Login there can be many User accounts – one in each database.

Now, if you backup a database on one server and restore it onto another server. It may contain Users within that database, that do not have a corresponding Login on the second server.

Execute this command to list any orphaned Users

SELECT 	DP.type_desc,
        DP.SID,
        DP.[name] UserName
FROM [sys].[database_principals] DP
LEFT JOIN [sys].[server_principals] SP
       ON DP.SID = SP.SID
WHERE SP.SID IS NULL
AND DP.authentication_type_desc = 'INSTANCE';

There are two types of User account – SQL and Windows. For orphaned SQL Users paste the name, SID, and password* into this command and execute it to create the missing Login.

(*If you do not know the Login password that was used on the old server then create a new one. They are not linked)

USE [master]
GO
CREATE LOGIN [SomeLogin]
WITH PASSWORD = 'SomePassword',  
SID = 0xSomeSid;

For orphaned Windows Users use this command …

USE [master]
GO
CREATE LOGIN [SomeDomain\SomeLogin] 
FROM WINDOWS 
WITH DEFAULT_DATABASE = [master];

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s