Help! My SQL Server 2008 ‘sa’ account got locked out and I can’t do anything with my databases!

I recently had an issue where the sa account for one of my SQL Servers got locked out. I assume the reason for the lock out was that the account was marked with Enforce password policy and someone had got the password wrong too many times. Regardless, I was no longer able to log in as sa.

Unfortunately there was a database on my server that only the sa account had access to, and it was starting to look like there was no way to get access back. To make matters worse, none of the other accounts had the correct access level to alter the sa account, and without the sa account, I was unable to elevate the access level of existing accounts. My server has mixed-mode authentication enabled but even domain administrator accounts couldn’t touch the sa account. It seemed like the perfect catch-22.

I Googled a lot of pages, many of which weren’t helpful, but then I found this:

Connect to SQL Server When System Administrators Are Locked Out

Good old Microsoft for once came up with the goods with an easy to follow, step-by-step guide to gaining access again.

The solution involved temporarily setting up single-user mode for the server, and then logging in to both the server and SQL Server Management Studio using an account with local machine administrator access. I was then able to remove the lock on the sa account, reset the password and gain access once more. Phew!

For all but production database servers, I would definitely recommend removing the Enforce password policy on all sa accounts in future. In the meantime, I hope this post has been useful.

Taking databases offline in SQL Server 2012

Whenever I try and use the Take Offline feature in SQL Server 2012 via SQL Server Management Studio, the progress window hangs, and no matter how long I wait, nothing seems to happens.

I found an alternative way to do this today, which solves the problem nicely. Simply run the following T-SQL:

ALTER DATABASE {DatabaseName} SET OFFLINE WITH ROLLBACK IMMEDIATE

…and the database goes offline, ready for other operations such as database restores to be performed.

NOT EXISTS queries

This is the second post in my “Aides-Memoires” category. It’s a bit more complex than my first aides-memoires post on linking JavaScript and CSS, but still pretty basic.

This time I’m tackling the NOT EXISTS query in SQL. Again this is something that I often forget the syntax for and have to Google.

Aside: I haven’t actually had to write a NOT EXISTS query for a number of years now, and with the proliferation of ORMs such as NHibernate and Microsoft Entity Framework I rarely write raw SQL now anyway. However, there are always going to be times when highly complex queries and operations are required, and when execution time is paramount. At those times the most efficient way to get the job done is often to execute SQL directly on the database server using stored procedures, particularly when temporary tables and indexes are required.

For those moments, here’s how to write a NOT EXISTS query…

Note that I’m using T-SQL in SQL Server for these examples, although they will probably work with most relational databases without too much tweaking given the basic nature of the SQL used.

First let’s make some tables:

CREATE TABLE [Table1]
(
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NOT NULL,
	CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)
)

CREATE TABLE [Table2]
(
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NOT NULL,
	CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)
)

Now we populate some test data. Note that records are missing from Table2:

TRUNCATE TABLE Table1
TRUNCATE TABLE Table2

INSERT INTO Table1 (Name) VALUES ('One')
INSERT INTO Table1 (Name) VALUES ('Two')
INSERT INTO Table1 (Name) VALUES ('Three')
INSERT INTO Table1 (Name) VALUES ('Four')
INSERT INTO Table1 (Name) VALUES ('Five')

INSERT INTO Table2 (Name) VALUES ('One')
INSERT INTO Table2 (Name) VALUES ('Four')
INSERT INTO Table2 (Name) VALUES ('Five')

SELECT * FROM Table1
SELECT * FROM Table2

Note that I am clearing down the tables each time I add data to ensure a clean starting position, and returning the contents of both tables to show what’s been added. Running the query gives the following results:

NOT EXISTS 1

NOT EXISTS 2

To find rows that exist in Table1 but not Table2, use the following:

SELECT 
	Name
FROM
	Table1 T1
WHERE 
	NOT EXISTS
    (
        SELECT  
			Name
        FROM    
			Table2 T2
        WHERE   
			T1.Name = T2.Name
    )

As expected, this returns the following results set:

NOT EXISTS 3

Finally, to find missing rows and insert them into Table2, use the following:

INSERT INTO Table2
(
	Name
)
SELECT 
	Name
FROM
	Table1 T1
WHERE 
	NOT EXISTS
    (
        SELECT  
			Name
        FROM    
			Table2 T2
        WHERE   
			T1.Name = T2.Name
    )

On execution, this reports:

(2 row(s) affected)

…and a quick look into the Table2 using:

SELECT * FROM Table2

Returns:

NOT EXISTS 6

…as expected. Note that the values are in an odd order, proving that Two and Three were inserted last.