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

New Versions of Useful Tools for SQL Server

There is a completely rewritten version of Geekbench, which is a very useful cross-platform processor and memory performance benchmark that you can use for comparing and validating processor and memory performance on anything from your laptop to a very expensive database server in just a few minutes, with no configuration of the benchmark needed. As the Primate Labs web site puts it:

Geekbench 3 is Primate Labs’ next-generation processor benchmark, with a new scoring system that separates single-core and multi-core performance, and new workloads that simulate real-world scenarios. Geekbench 3 makes it easier than ever to find out if your computer is up to speed.

There is also a new 1.66 version of CPU-Z, that has support for some upcoming processors, such as the Intel Xeon E5-2600 v2 series (Ivy Bridge-EP).

image

SQL Server 2012 Diagnostic Information Queries for August 2013

I have gone through and made some minor updates and bug fixes for all of my SQL Server 2012 Diagnostic Information Queries for this month, along with adding two new queries at the end of the script. The links for the queries and the blank results spreadsheet are below.

SQL Server 2012 Diagnostic Information Queries                SQL Server 2012 Blank Results

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master, system database.

The idea is that you would run each query in the set, one at a time (after reading the directions). You need to click on the top left square of the results grid to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the spreadsheet.

There are also some comments on how to interpret the results after each query. Please let me know what you think of these queries.