Fix SQL Server Orphaned Users After Database Restore

I have been having an issue upgrading the DotNetNuke install for my church, so I thought I'd take a back-up and restore it on my laptop and then figure out what went wrong. If you have ever restored a back from a hosting server or just from a production environment to your local machine you have had this issue.

The reason for the problem is that the users that are defined in the database do not match the users that are in your current SQL Server install. You might find that you look at your server and see a user with the same user name but the GUID will not match.

Here are some SQL stored procedures that can help fix this issue:

List all the orphaned users in a database:

EXEC sp_change_users_login 'report'

If the user id already exists you can fix the GUID problem with this:

EXEC sp_change_users_login 'Auto_Fix' , 'user'

If the user id does not exists you can get the orphaned id created in the database with this:

EXEC sp_change_users_login 'Auto_Fix' , 'user' , 'login' , 'password'

I finally got it all worked out by adding the needed user to the server and then running the EXEC sp_change_users_login 'Auto_Fix', 'user' which produced the following output:

The row for user 'user' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

blog comments powered by Disqus


<<  August 2022  >>

View posts in large calendar

Month List