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’
Exec dbo.sp_help_revlogin idb_dev
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.
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.
— 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.