SQL_users_logins_sync_SIDs.sql

/* Run against the restored database */

SET NOCOUNT ON;

-- Check for database Users and server-level logins having the same names but
-- differing SIDs.  Generate an Alter User statement to remap the User to the
-- Login's SID.
SELECT  l.name AS LoginUserName, l.sid AS LoginSID, u.sid AS UserSID,
        'ALTER USER ' + QUOTENAME(u.name) + ' WITH LOGIN = ' +
        QUOTENAME(l.name) + ';' AS SyncCmd
FROM    sys.server_principals l
        INNER JOIN sys.database_principals u ON u.name = l.name
WHERE   l.type = 'S' AND
        u.sid <> l.sid;

/*
    -- Example of query output
    -- Execute generated statements to sync Users with Logins
    ALTER USER [AppUser] WITH LOGIN = [AppUser];


    -- Update the scripts below with a User/Login name then run them to verify
    -- that the SIDs now match
    SELECT name, sid FROM sys.server_principals WHERE name = N'AppUser';
    SELECT name, sid FROM sys.database_principals WHERE name = N'AppUser';

*/