0

I am working in Sql Server 2008. The Issue is when i restore the database the database users get disable i can map that particular user with the Login user. For mapping i have to remove the user from the database and then map the user.

Eg:- Database name :- Clv Database User :- Clv Server Loging :- Clv this user is mapped with the Clv Database Clv User

Then i took full backup of the Clv database. Now i want to change the server of the database so i restore the backup in new server.

In new server i Create Clv login and trying to map it with the Clv database's Clv user but the error is there is already a Clv User so i delete the user of the database and then i map user again so its done on that way but i want to know that is there any other way out for this situation or there is only this wayout.

Now when i restore the database the The Clv database User no longer mapped with the Clv Login. For to doing That i have to remove Clv database user first and the Map with Clv Login.

Is then any other solution??

3 Answers 3

0

Yes, first check for orphaned users on the restored database with this statement:

sp_change_users_login @Action='Report'

Then remap orphaned users with this one. You have to substitute with the orphaned user and with the actual database login

sp_change_users_login @Action='update_one', @UserNamePattern='', @LoginName=''

0

There is a related question on Stack Overflow. The answer by abalogh points to a very useful Microsoft support link that gives information about this problem and offers multiple solutions.

0

Users are mapped to logins not by name, but by an identifier called a sid (security identifier). Luckily, you have visibility into those and can find the ones that don't match. The sys.database_principals view has a column called sid that maps to a column in sys.server_principals called sid. Do a left join between them to find the users in your database that don't map to a login. Since you're working on SQL 2008, the preferred method to change the login that a user is associated with is "alter user [foo] with login = [bar]"

An alternative is to create the logins with the right sid on your destination server so that when you restore your database, the sid matches up. The syntax for that is "create login [bar] with sid = 0xyour_sid_here...". Hope this helps.

You must log in to answer this question.