Fix login of restored DB (SQL server 2005)

After when restored Database from *.bak file. The associated login will not be restored into the MSSQL.

And when trying to create the same login in the security tab in managemement studio. It will prompt

Error 15023: User already exists in current database.

The off hand workaround that normally one would do would be drop the user in the database user login and recreate again.
like
USE YourDB
GO
EXEC sp_dropuser 'YourRestoredDBLogin'
GO

The better way would be

run command below to check the orphan logins.

USE YourDB
GO
EXEC sp_change_users_login 'Report'
GO

then below to restored the login with command below. It will retain the settings that you have.

USE YourDB
GO
EXEC sp_change_users_login 'Auto_Fix', 'YourRestoredDBLogin', NULL, 'YourRestoredDBLoginPassword'
GO


Reference: http://blog.sqlauthority.com/2007/02/15/sql-server-fix-error-15023-user-already-exists-in-current-database/

Comments

CV said…
Thanks for the post.
I used to use this a lot but today just forgot that there is a script which does this job.
Your blog reminded me.
Thanks again!

Popular posts from this blog

Clearcase check in/uncheck out all the checked out files script

MSSQL GROUP_CONCAT

Duplicating a local copy of project from TFS and making web software client factory to work