Change Login Name

Question

i have a problem when i restore DB_1 database backup into DB_2 database. but Login Name for user dbo is missing (). how i can re-create the Login Name?? i use SQL 2000

Answer 1

Add 'sa' as the databaseowner so add 'sa' to the db owner role of the DB_2 catalog before you restore the backup to that catalog.

Btw, sorry if I sound rude, but stackoverflow is mainly for programming questions, not sysadmin questions, I think if you ask your question on a sqlserver oriented board you'll get more responses.

Answer 2

What you have here is called an orphaned user. i.e. the user exists in the database you've restored, but it isn't setup as a login on the database server.

If the user is a windows integrated login, the adding the login to the database server is all you need to do. If its a SQL Server login, then it's a little tricker:

To get a report of that orphaned users are in your restored database, run:

USE restored_database
GO
exec sp_change_users_login @Action='Report';
GO

To re-create the database at login & have it linked to the restored database run:

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

Answer 3

Call sp_dropuser 'user' on the restored database.

If the user already exist on the server, give the user permission to the restored database, otherwise re-create the user on the server and then give permission to the restored database.