nickrjsmith
asked on
Using sp_change_users_login 'auto_fix'
How can i use sp_change_users_login 'auto_fix' to update all my logins on a server so i don;t have to use
exec sp_change_users_login 'update_one',
'nsmith', 'nsmith'
over and over again.
exec sp_change_users_login 'update_one',
'nsmith', 'nsmith'
over and over again.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
(thanks for help.. i'm aware of the security issues)
sp_change_users_login 'Auto_Fix'
needs parameters... i get
Server: Msg 15290, Level 16, State 1, Procedure sp_change_users_login, Line 137
Terminating this procedure. The Action 'Auto_Fix' is incompatible with the other parameter values ('(null)', '(null)').
sp_change_users_login 'Auto_Fix'
needs parameters... i get
Server: Msg 15290, Level 16, State 1, Procedure sp_change_users_login, Line 137
Terminating this procedure. The Action 'Auto_Fix' is incompatible with the other parameter values ('(null)', '(null)').
sp_change_users_login 'Auto_Fix' , '%'
ASKER
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 0.
I think i need to pass in the login namse from the master db?
seems that although the parameter is named UserNamePattern, you cannot use wildcards....
see here:
http://rage.against.org/SQLFixLogins?show_comments=1
see here:
http://rage.against.org/SQLFixLogins?show_comments=1
BUG: Using the Auto_Fix Option with sp_change_users_login Can Leave Security Vulnerabilities
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/kb/246133