How to run “sp_change_users_login” for all users in a database
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.
