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.