Categories
Tech

Creating a local SQL admin user when you’re locked out

Please only use this for good. Restart SQL in single-user mode then check the commands section for commands.

Please only use this for good.
Make sure you have a backup before doing this.

You can find a powershell script for this here.

You’ll need to be running mixed mode. If you’re not already running mixed mode here’s a post on how to change server authentication modes.

Short version

Restart SQL in single-user mode then check the commands section for commands

Finding SQLServer.Exe

Open services.msc and look for the service named SQL Server (<instance name>) this will be SQL Server (MSSQLSERVER) by default.

services.msc

Copy Path to executable for the path to sqlserver.exe. You do not need the -s<instance name> at the end.

SQL single user mode

Open a command prompt (run as admin) and cd to the directory that contains sqlserver.exe found above.

cd "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\"

Stop the SQL instance service with net stop <instance name>

net stop MSSQLSERVER

Run sqlservr.exe -m to start SQL in single-user mode. Keep this window open.

sqlservr.exe -m

sqlservr args
-m: single use mode

Creating user

Open a new command prompt (run as admin) and connect to the SQL instance with sqlcmd -E. You can use -S if you need to specify the server. -E and -S are case sensitive.

sqlcmd -E

sqlcmd args
-E: use trusted connection
-S: [protocol:]server[instance_name][,port]

Commands

Create a new user with CREATE LOGIN newUser with PASSWORD = ‘Password!’;

CREATE LOGIN newUser with PASSWORD = 'Password!';

GO to commit

GO

Add the new user to the sysadmin role with SP_ADDSRVROLEMEMBER ‘newUser’, ‘sysadmin’;

SP_ADDSRVROLEMEMBER 'newUser', 'sysadmin';

GO to commit

GO

Type EXIT to quit

EXIT

Restarting SQL

alt+tab back to the command prompt window opened in the SQL single user mode step above and press CTRL+C.
Press Y at the Do you wish to shutdown SQL Server (Y/N)? prompt.

Start the SQL instance service with net stop <instance name>

net start MSSQLSERVER

If you just copied and pasted everything the username is newUser and password is Password!. Remember to delete this account when you’re done.

Leave a Reply

Your email address will not be published. Required fields are marked *