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