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

Calendar

<<  August 2022  >>
MonTueWedThuFriSatSun
25262728293031
1234567
891011121314
15161718192021
22232425262728
2930311234

View posts in large calendar

Month List