TechBubbles Microsoft Technology BLOG

Login mapping errors on restored database

If you move a database from one server to another using backup\restore or detach/attach option then there is a chance of users become orphaned. Users logins mapping done by the SID and if there is a login present on the destination instance with same name but another SID then the user will not recognize and orphaned. Login mapping problem occurs only with SQL Logins when you move the database to another server on same domain. You would not find any problem in migrating widows logins as their SID is same as domain SID set in active directory.

Follow the below steps to identify and correct the orphaned users in restored or attached database

1. Identify Orphaned SQL users using below query

   1: SELECT, dp.sid

   2: FROM sys.database_principals dp

   3: LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid

   4: WHERE sp.sid IS NULL AND

   5: dp.type_desc = 'SQL_USER' AND

   6: dp.principal_id > 4;

2. Use the below command to re-map user to an existing login


About the author

Kalyan Bandarupalli

My name is kalyan, I am a software architect and builds the applications using Microsoft .NET technologies. Here I am trying to share what I feel and what I think with whoever comes along wandering to Internet home of mine.I hope that this page and its contents will speak for me and that is the reason I am not going to say anything specially about my self here.

Add Comment

TechBubbles Microsoft Technology BLOG

Follow me


Tag Cloud