Resolve Orphaned Users In SQL Server

Many times where you copy a databese from a SQL Server instance to another, some user get orphaned. This situations is because of user database exist (with the copy, export/import…) and the login doesn’t.

 
You can get a report of orphaned users in a database with the next stored procedure;

 
USE demodb
GO
sp_change_users_login @Action='Report'
GO    

BHappy        0x769008D2CDC6694F9A2230A4786901C1
Financial	    0x6FBF6620832EC54181A3520F9257EEC0
Financialweb	0xB5A86A3BDC7E5D44AD48AD931D15F88E

i) Case i login not exist on destinity;

From origin generate the create login script, the ouput is similiar to show below;

 
CREATE LOGIN [BHappy] WITH PASSWORD=N'Æa¦m²}Ù?2RêtÖ??w¯çbªR®+ä?xjëEg', DEFAULT_DATABASE=[Financial], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

After that the user, you need link de user database to the new login;

 
EXEC sp_change_users_login 'Auto_Fix', 'BHappy'

The output is similar to this;

 
The row for user 'BHappy' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

ii) Login exist in destinity:

Execute de next stored procedured to link both;

 
use demodb

EXEC sp_change_users_login 'Auto_Fix', 'Financial'

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s