This issue happens often when restoring databases from one server to another.
Let's say we have a database A on Server X.
If database A has a user user1. It most likely maps to a SQL login user1, with its own SID (unique identifier).
If we restore database A on server Y, it will attempt to map the user1 to a SQL login on server Y, by using the SID. Since the SQL login user1 exists on server Y, but with a different SID, the mapping doesn't happen, hence the user on the database gets orphaned.
We fix that by running
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
However, below we have a cursor that identifies orphaned users and synchronized them.
SET NOCOUNT ON USE [database name goes here] --Variable to hold current UserName to process DECLARE @UserName nvarchar(255) DECLARE @rowcount int --Create Table to hold the orphan Users DECLARE @OrphanUserTable table (rownum int identity(1,1), UserName sysname) insert into @OrphanUserTable SELECT a.name FROM sysusers a inner join master..syslogins b on a.name =b.name WHERE a.issqluser = 1 AND a.sid is not null AND a.sid <> 0X01 AND suser_sname(a.sid) is null ORDER BY a.name IF (select count(UserName) from @OrphanUserTable)=0 PRINT 'No orphan users found' SET @rowcount = 1 WHILE @rowcount < = (select count(UserName) from @OrphanUserTable) BEGIN PRINT @UserName + ' user name being resynced' --Re-sync the currently selected orphan user EXEC sp_change_users_login 'Update_one', @UserName, @UserName --Get Next Orphan User SET @rowcount = @rowcount + 1 END