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


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.


Comments (0)