How to run “sp_change_users_login” for all users in a database

Chris Skorlinski
Microsoft SQL Server Escalation Services

Following a user database restore the database had orphaned users.  We used TSQL commands below to build a list of sp_change_users_login commands in order to resynchronize database users with SQL logins.  We then ran these sp_change_users_login commands to automatically fix the users. In SQL 2005 and SQL 2008 the SQL login is created and automatically remapped to the database user.

In the sp_change_users_login you specify the new SQL Login.  The first script uses a random characters string for the password, the second, much less secure, sets password to their SQL user login.

--Random password

select 'sp_change_users_login auto_fix, ' + CHAR(39) + name + CHAR(39) + ', NULL, '

+ CHAR(39)  + 'B3r12-3x$098f6' + CHAR(39)

+ CHAR(13) + CHAR(10)+ 'Go'

from sys.sysusers

where islogin = 1 and issqluser =1 and hasdbaccess = 1 and sid > 0x01

 

--User name as password

select 'sp_change_users_login auto_fix, ' + CHAR(39) + name + CHAR(39) + ', NULL, '

+ CHAR(39) + name  +  CHAR(39)

+ CHAR(13) + CHAR(10)+ 'Go'

from sys.sysusers

where islogin = 1 and issqluser =1 and hasdbaccess = 1 and sid > 0x01

Output:
-----------------
sp_change_users_login auto_fix, 'PubUser', NULL, 'PubUser'
Go

Then copied and pasted the output into New Query and executed to correct the login mapping.

Output:
-----------------
Barring a conflict, the row for user 'PubUser' will be fixed by updating its link to a new login.
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 1.