Saturday, 24 October 2015

Fixing Orphaned Users Post SQL Database Migration

Fixing Orphaned Users


Its very common that few of your SQL Users become orphaned (Orphaned users are the users that are not able to connect to the database after you migrate your SQL Server Database from one SQL Server to another SQL Server). Today we will discuss how to fix orphaned users after we have migrated the sql database

The quickiest way to fix this is delete the user from the restored database and then recreate the user and corresponding permission to the database.

If the user owns a schema in the database, you won’t be able to delete the user. Then you can use the special stored procedure sp_change_users_login 'report'. This will give you a list of all the orphaned users in the database.


If you already have a login id and password for this sqlid, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'sqlid'
If you want to create a new login id and password for this sqlid, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'sqlid', 'login', 'password'

This is the quickest way of fixing orphaned users.

1 comment:

  1. This article is really helpful for me. I am regular visitor to this blog. Share such kind of article more in future. Personally i like this article a lot and you can have a look at my services also: I was seriously search for a Salesforce training institutes in ameerpet which offer job assistance and Salesforce training institutes in Hyderabad who are providing certification material. It's worth to join Salesforce training institutes in India because of their real time projects material and 24x7 support from customer desk. You can easily find the best Salesforce training institutes in kukatpally kphb which are also a part of Pega training institutes in hyderabad. This is amazing to join Data science training institutes in ameerpet who are quire popular with Selenium training institutes in ameerpet and trending coureses like Java training institutes in ameerpet and data science related programming coures python training institutes in ameerpet If you want HCM course then this workday training institutes in ameerpet is best for you to get job on workday.

    ReplyDelete