Monday, 30 July 2012

Orphaned users

I'm sure many people have experienced the need to "reconnect" orphaned users after restoring a database from a different server. Not a difficult remedy, just run exec sp_change_users_login 'auto_fix','user_name' and you're good. If you run sp_change_users_login 'Report' first you can find all orphaned users for the given database.

Today I bumped into a situation where I ran the SP and received the error "An Invalid parameter or option was specified for 'sys.sp_change_users_users_login'". I was perplexed as I've run the sp many times before with no problems. I was starting to tear my hair out having searched the web with no joy until, and in hindsight, this is silly, I looked to see if the login existed on the server. And guess what, it didn't.

So, as is often the case, the Microsoft error message is, if not wrong, then at least misleading! I would have expected to see an error like 'User doesn't exist' or similar. Alas, in future, when this error appears, check whether the login exists.

See my other post with a query to resolve any and all orphaned users on a database.