Recovering sysadmin on your local instance

Ran across the issue where my SQLServer was installed without my knowledge, and needed to recover sysadmin access to it.

Step 1 : Check if you can log into sa

Sometimes the sa account still has the default password that comes with the instance. Each version is different, check your version’s default password online. In my case, because the instance was installed vanilla (right out of the box), the sa account is disabled, and with my permissions, I can’t enable it.

If you’re really lucky, you can just log in with the sa account and fix all your stuff. But it’s most likely that you’re not…

Step 2 : Force yourself in with single-user SQLCMD

Stack Overflow has a great guide, but I had SQLExpress, so my steps were slightly different.

First, figure out exactly what your SQL login (most likely windows auth login) is. If you can log into the server, you can expand the security > Logins to find your exact login, complete with domain. If you can’t log in at all, you won’t have a login, and we can fix that too.

Start > SQL Server > Configuration Tools > SQL Configuration Manager

Right-click on the appropriate instance of ‘SQL Server’ and Stop the service. If you can’t stop, make sure you’ve exited out of SMSS.

Start > run > cmd.exe
sqlserver.exe -sSQLExpress -m"SQLCMD"

This starts a new instance of SQLCMD as single-user mode. My instance is SQLExpress.

Start > run > cmd.exe
SQLCMD -S localhost\SQLExpress

Your first cmd window is keeping the SQLCMD connection open, you’ll need a new window (this one) to do SQL. My instance is SQLExpress.

SQLCMD -S localhost\SQLExpress

CREATE LOGIN [testAdmin] WITH PASSWORD=N'test@1234', DEFAULT_DATABASE=[master]
go

EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAIN\USERNAME>>', @rolename = N'sysadmin'
go

If you can log into the instance, you already have the login and don’t need to perform the first two lines that creates your account. Notice the ‘go’s after each command. If you don’t have them, SQL will not do anything ; neither with the go’s or without, it will not tell you one way or another if the action was performed or is successful – only if there was an error.

After you’ve done this, CTRL-C in your SQL CMD window (current window), and CTRL-C in your other (first) window. Start up the instance again in SQL Configuration Manager and see how you did.