Taking ownership of sqlserver (or sql express)

From time to time it happens that you install a sql server (or SQL express) and that you forget to assign local admin permissions. When this happens, you can choose to do a reinstallation... or you can try to assign the permissions after all through a backdoor. I chose the latter!  

Open a cmd prompt (as admin) {% highlight batchfile lineos %} net stop mssqlserver {% endhighlight %} Now, in this command prompt, navigate to where SQL server is installed. The directory is typically something like this (where MSSQL13.SQLEXPRESS is replaced with the version and instance type that was installed) {% highlight batchfile lineos %} C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Binn {% endhighlight %} Now, in the same window, 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"

As we were using SQLEXPRESS in this post, we received the following error:

this is because sqlexpress is not installed as a "fully functional" sql server. In this case, you also need to specify the name of the instance explecitely:

sqlservr -m"SQLCMD" -s SQLEXPRESS

Now, open another command prompt window as the same user (and as Administrator) and then run:

SQLCMD -S .\sqlexpress -E -Q "if not exists(select * from sys.server_principals where name='<yourUser>') CREATE LOGIN [<yourUser>] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = '<yourUser>', @rolename = 'sysadmin'"

<yourUser> can be a local user, a domain user or a even a group just be aware that you typically want to limit who has admin access!

Now, in the first command prompt, stop SQL server with the Ctrl + C key combo and then start the sqlserver service again:

net start mssqlserver

I once wrote a very similar blogpost, but at some point in time my previous blog got lost and that post was lost to. In the last few weeks, I really needed this info again and therefore I decided to write it down again and to put it back online. I hope this will be useful for you too!