SQL Server – Fix – Orphan login Error using sp_change_users_login system stored procedure

As SQL DBA, we often deal with orphan user related issues while moving database from one server to another. We got an email from client saying they are unable to connect database server after database movement … Login Eror Message was “Login failed for user ‘idb_dev’.(Microsoft SQL Server, Error: 18456)”.  also Error 15023: User already exists in current database, It is simple example of orphan logins

To fix orphan login issue, sql server provides system stored procedure called sp_change_users_login. This will help you to re-link the login id with database user in sql server

Here is what I did.

On sql server Instance1:

1. I ran dbo.sp_help_revlogin (free download link by microsoft : http://support.microsoft.com/kb/918992) with login name to script out the login with its password ‘loginname’

Example:

Exec dbo.sp_help_revlogin idb_dev

On instance2\sql2008

2. Dropped login idb_dev
3. I ran the generated script from the previous step like below:

/* sp_help_revlogin script
** Generated Oct 15 2013 4:25PM on Instance1 */

— Login: idb_dev
CREATE LOGIN [idb_dev] WITH PASSWORD = 0x01007CE5D98453A1233FGLHK77PS009I823CD HASHED,
SID = 0xB59C2E199D25CD47AC3A05D1D434CD44,
DEFAULT_DATABASE = [master],
CHECK_POLICY = ON,
CHECK_EXPIRATION = OFF

4. I was expecting to see  the idb_dev login having access on database. so ran sp_change_users_login ‘Report’, saw some orphan login that was not mapped to any database user.

Example:

Exec sp_change_users_login ‘Report’

Since the login was not linked to the database user, I had to do this manually here. Use below step to fix login/user issue.

USE db_Dev

GO

— exec sp_change_users_login update_one, login_name, db_user

EXEC sp_change_users_login update_one, idb_dev, idb_dev

Finally problem resolved after updating user with login on instance 2.

SQL Server Database Migration Checklist

Leave a Reply

  

  

  


9 + 8 =