Restore Orphaned Users in MS SQL

[SamQuon]

I ran into an issue with connecting to a geodatabase in MS SQL after restoring the database from a backup. The restore process created several orphaned users. I was able to identify the orphaned users using the following query in Microsoft Management Studio.

SELECT dp.type_desc, dp.SID, 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'; 

This generated the list of users based on missing SQL Server authenticated logins.

Next I needed to map the orphaned user to the existing login with the following query in Microsoft Management Studio.

ALTER USER <user_name> WITH Login = <login_name>;

The source of the solution came from: https://msdn.microsoft.com/en-us/library/ms175475.aspx

[George_Thompson]

Hi Sam,

You need to re-sync the users in the instance to the geodatabase in SQL Server. Here is an example:

USE [database_name]

GO

EXEC sp_change_users_login 'Update_One', 'sde', 'sde'

GO

**Repeat EXEC for each data owner**

EXEC sp_change_users_login 'Update_One', 'GIS', 'GIS'

GO

How To: Re-synch SQL Server logins or users after restoring a database from backup

Restore a Database Backup Using SSMS 

[BirajaNayak]

In addition to George suggestion,

you can use the following syntax to resynch:

exec sp_change_users_login 'Auto_fix','gissrvadm', NULL, 'gissrvadm'

Thanks,

Biraja