How to avoid “Orphaned Users” with SQL Server 2005 Database Mirroring

If you are thinking about using SQL Server 2005 Database Mirroring, (which works quite well), you will want to take steps to avoid the "orphaned" database user problem when you fail-over from Principal to Mirror or vice-versa. 

A typical scenario for database mirroring is two servers, Server "A" and Server ‘B". You have a database, database "TestA" that is running on Server "A". In order for any application to access the "TestA" database on Server "A", you have to create a server login ("yourloginname") on Server "A", then you have to create a User in the "TestA" database that is associated with the "yourloginname" server login. When you create a login (at the server level), by default, SQL Server 2005 assigns it a unique SID.

If you then repeat this process on Server "B" (where the mirror database will run), the SID’s for the server login will be different between Server "A" and Server "B". Then, when you failover the mirrored database from Server "A" to Server "B", the database user in the database will still be associated with the different SID for the server login back on Server "A", and logins will fail. This is the orphaned user problem.

One way to fix this (after the fact) is to run an "Fix Orphaned user" script for each user in each database, (like you see below:

 

— Fix orphaned user
USE DatabaseName
GO
sp_change_users_login @Action=’update_one’, @UserNamePattern=’yourdatabaseusername’, @LoginName=’yourserverloginname’;

 

A better solution is to avoid the problem by using CREATE LOGIN to create a server login with a matching SID, ahead of time.

— Get login and SID information from server where Principal database is running
SELECT name, sid, type_desc
FROM sys.server_principals
WHERE [name] = ‘yourserverloginname’

— Add login to server where Mirror database will be running (the SID must be the same as what it is on the server where the Principal database is running)
CREATE LOGIN yourserverloginname with password = ‘yourloginpassword’, sid = 0xA306A5DFBF321A4D98D71520DAE1C1D3, DEFAULT_DATABASE = master, CHECK_POLICY = OFF

Technorati Tag: SQL Server

This entry was posted in SQL Server 2005. Bookmark the permalink.

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