sqlskills-logo-2015-white.png

How To Avoid Orphaned Database 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 SQL Server login on your database server. When you do this, SQL Server generates a unique SID for that SQL Server login. After you create the SQL Server login, you typically create a database user in a user database on that server instance, and associate the database user with that SQL Server login.

This works fine until you try to restore that user database to another SQL Server instance. If you previously created a SQL Server login with the same UserID on the new server, the SID for that SQL Server login will not match the database user in the user database that you have restored (from the other database instance). 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 from one instance to the other instance. It is also an issue with log shipping, and it often comes up when you migrate from an old database server to a new database server.

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 server login using the same SID as on the original server. Just like you see below:

-- Get Sids for all SQL Server logins on the old server instance
SELECT name, [sid] 
FROM sys.server_principals
WHERE [type] = 's'; 

-- Create new SQL Login on new server instance
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SQLAppUser')
    DROP LOGIN SQLAppUser;
GO

-- Use the sid from the old server instance 
CREATE LOGIN SQLAppUser WITH PASSWORD = N'YourStrongPassword#', sid = 0x2F5B769F543973419BCEF78DE9FC1A64,
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO

6 thoughts on “How To Avoid Orphaned Database Users with SQL Server Authentication”

  2. Glenn,

    Will the following fix the issue by mapping the SQL Login on the new instance to the User in the database and updating the SID for the User as well?

    USE database_name;
    GO

    ALTER USER [database user] WITH LOGIN = [sql language=”login”][/sql];

    Reply

  3. Hi Glenn

    I’ve always used the sp_change_users_login proc to remap orphaned users to the correct login rather than creating them with the original SID. Just wondering if this (i.e. the proc) isn’t the best way or if there’s other advantages to creating the user with the original SID?

    Cheers

    Reply

  4. Idera offers a free tool which creates all of that very nicely, including the passwords and authorizations for the users regardless of the authentication type. Just migrated about 40 DBs from multiple instances from a cluster to standalone servers (don’t ask but they’re using VMs instead of the cluster for the HA) and it created the scripts and pushed over the authorizations nicely. Since I couldn’t go back to the old servers as they old and new servers used the same alias, it was the greatest thing since sliced bread. (For some reason I was given the new SQL instances with a higher patch level than the old one too, so migrating the model, master and msdb was out of the question).

    It’s worth checking out.
    http://www.idera.com/productssolutions/freetools/sqlpermissionsextractor

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course