How to Fix Orphaned Users in SQL Server Database


Aug 24, 2012

Introduction

In this article we will take a look at the steps a database administrator needs to follow in order to fix orphaned users within a SQL Server Database.

When will you see Orphaned Users within a SQL Server Database?

You may end up seeing orphaned uses within a SQL Server Database when you have restored a database from one server to another or you want to attach a database which was detached from another SQL Server Instance.

For example for business reasons you may have to restoring a copy of your Production database on to a QA or a Development Server. In such scenarios your application login would be already present on the QA and Development Server. However, the SIDs for the application login would be different on each server for the same login resulting in Orphaned User.

Identifying Orphaned Users in SQL Server Database

Use MyTechMantra
GO

sp_change_users_login 'report'
GO

Fixing Orphaned Users in a Database when Database Login and SQL Server Login is already available in SQL Server

Use MyTechMantra
GO

sp_change_users_login 'AUTO_FIX','MyTechMantra'
GO

How to Link the Specified User in a Current Database to an existing SQL Server Login

/* Create a New SQL Login */

CREATE LOGIN NewSQLLogin WITH PASSWORD = 'ERT#$%dfg#01'
GO

/* Map Existing Database User MyTechMantra to Login NewSQLLogin */

USE MyTechMantra
GO
EXEC sp_change_users_login 'Update_One', 'MyTechMantra', 'NewSQLLogin'
GO

Reference:- sp_change_users_login (Transact-SQL)

Conclusion

In this article you have seen how to fix Orphaned Users in SQL Server Database.

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."