FIX SQL Orphaned Users (Create SQL Users exists in a restored database in SQL Users)

Have you ever restored a database from your backup and wanted an easy way to get users automatically created? script below will do this task for you:

 

DECLARE @USER varchar(500)
DECLARE @SID  varchar(500)

DECLARE CURR CURSOR FOR
SELECT NAME, SID FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null ORDER BY name
OPEN CURR
FETCH NEXT FROM CURR INTO @USER, @SID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'Auto_Fix', @USER, @SID , @SID
FETCH NEXT FROM CURR INTO @USER, @SID
END
CLOSE CURR
DEALLOCATE CURR

Above script will read all database level defined users and locate it corresponding users in SQL, if it found the script will map existing user to SQL user, otherwise it will add a new user with a new password and map both users.

Next step required is to change the user password at the application level “Dynamics GP” so it encrypts the user password again and fulfill business requirements.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
me@mohdaoud.com
www.mohdaoud.com