Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

It's fairly straight forward to fix up a single orphaned SQL user to a login using:

EXEC sp_change_users_login 'Auto_Fix', 'user'

I could script this, but is there an existing sproc which automatically tries to fix up every orphaned user in a given database?

share|improve this question

migrated from Feb 13 '12 at 19:58

This question came from our site for professional and enthusiast programmers.

+1 Thanks that's a nice script. – Daniel James Bryars Feb 13 '12 at 17:28
@AaronBertrand: make that an answer, with some padding and fluff? – gbn Feb 14 '12 at 15:44
@gbn have done so, thanks – Aaron Bertrand Feb 14 '12 at 16:25
up vote 11 down vote accepted

Ted Krueger (@onpnt on twitter) wrote a great script that does this. It adds logins for any user without a login and runs the auto_fix. He even wrote one that includes fixing Windows logins:

Of course if you want to test it first (or merely perform an audit) you can comment out the action lines (EXEC) and just print out the results.

share|improve this answer
+1 Nice "padding and fluff"! – Daniel James Bryars Feb 20 '12 at 23:21

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.