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.
nickrjsmithAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
sp_change_users_login 'Auto_Fix'  (case sensitive)

From Books online:
Links user entries in the sysusers table in the current database to logins of the same name in syslogins. It is recommended that the result from the Auto_Fix statement be checked to confirm that the links made are the intended outcome. Avoid using Auto_Fix in security-sensitive situations. Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended.
user must be a valid user in the current database, and login must be NULL, a zero-length string (''), or not specified.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sajuksCommented:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q298758
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

nickrjsmithAuthor Commented:
(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)').
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
sp_change_users_login 'Auto_Fix' , '%'
nickrjsmithAuthor Commented:

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?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
seems that although the parameter is named UserNamePattern, you cannot use wildcards....

see here:
http://rage.against.org/SQLFixLogins?show_comments=1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.