After restoring a database on a different server than the one it is coming from, it will contain orphaned users. Here is how to find these orphaned users:

exec sp_change_users_login 'report'

In order to fix this, the logins for the reported users will have to be created on the new server if they do not exist.

If the logins are the same as on the original server, it is easy to fix the orphaned users. Simply issue the following command for each orphaned user:

exec sp_change_users_login 'auto_fix', '<replace this by the username...>'

UPDATE:

For some reason I don’t understand, I got an error today when running this command:

An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’

But I found a workaround. The command executed fine when I added the @Password parameter:

exec sp_change_users_login 'auto_fix', '<replace this by the username...>', @Password = '<Replace this by the proper password>'

Leave a Reply

Your email address will not be published. Required fields are marked *