Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 stackoverflow.com Feb 13 '12 at 19:58

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

3  
    
+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

1 Answer 1

up vote 8 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:

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/fixing-orphaned-database-users

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

 
discard

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.