How To Avoid Orphaned Users With SQL Server Authentication

One common issue that database administrators often run into is the old, familiar “orphaned” user problem. This happens when you use SQL Server Authentication to create a server login. When you do this, SQL Server generates a SID for that login. Then, you create a database user on that server instance, and associate it with that login.

This works fine until you try to restore that database to another server. If you previously created a SQL Server Authentication login with the same UserID on the new server, the SID for that login will not match the database user in the database that you have restored. Hence the term “orphaned” user.  This is an especially big issue if you are using database mirroring, since your database users will be orphans when you failover.

There are several ways to fix this, but the best thing (outside of just using Windows Authentication and avoiding the problem), is to create the new login using the same SID as on the original server. Just like you see below:

-- Get Sid for SQL Server logins on "Principal" server instance
SELECT name, [sid] 
FROM sys.server_principals
WHERE [type] = 's' 


-- Create SQL Login on "Mirror" server instance
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SQLAppUser')
DROP LOGIN [SQLAppUser]
GO 
CREATE LOGIN SQLAppUser WITH PASSWORD = N'1994Acura#', sid = 0x2F5B769F543973419BCEF78DE9FC1A64,
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO 
Technorati Tags:

Advertisements
This entry was posted in SQL Server 2008. 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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s