Showing posts with label Orphaned Users. Show all posts
Showing posts with label Orphaned Users. Show all posts

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.