How do I grant myself admin access to a local SQL Server instance?

by

Open a command prompt window. If you have a default instance of SQL Server already running, run the following command on the command prompt to stop the SQL Server service:

net stop mssqlserver

Now go to the directory where SQL server is installed. The directory can for instance be one of these:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn

Figure out your MSSQL directory and CD into it as such:

CD C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

Now run the following command to start SQL Server in single user mode. As
SQLCMD is being specified, only one SQLCMD connection can be made (from another command prompt window).

sqlservr -m"SQLCMD"

Now, open another command prompt window as the same user as the one that started SQL Server in single user mode above, and in it, run:

sqlcmd

And press enter. Now you can execute SQL statements against the SQL Server instance running in single user mode:

create login [<<DOMAIN\USERNAME>>] from windows;

-- For older versions of SQL Server:
EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAIN\USERNAME>>', @rolename = N'sysadmin';

-- For newer versions of SQL Server:
ALTER SERVER ROLE [sysadmin] ADD MEMBER [<<DOMAIN\USERNAME>>];

GO

Source.

UPDATED
Do not forget a semicolon after ALTER SERVER ROLE [sysadmin] ADD MEMBER [<<DOMAIN\USERNAME>>]; and do not add extra semicolon after GO or the command never executes.

Leave a Comment