Add Yourself as a SQL Server Express SysAdmin

Add_Yourself_As_A_Sql_Server_Express_SysAdmin.txt

Adding Yourself as SQL Server Express SysAdmin
----------------------------------------------

Problem:

The help desk installs SQL Server on our developer machines under their own user accounts meaning you aren't a 
sysadmin on your own SQL Server Express instance which in turn means you can't create a new database.

Solution:

Follow the direction below to manually add yourself as a sysadmin.

1.  Shut down the SQL Server (SQLEXPRESS) windows service

2.  Open a command window (as admin) and run single-user mode as local admin with this command:

"c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -m -s SQLEXPRESS

3.  Open another cmd window (as admin)

4.  open sqlcmd:

sqlcmd -S .\SQLEXPRESS

Now add the sysadmin user ( Substitute your own domain and user name in the example below ):

a.  sp_addsrvrolemember 'YourDomainName\YourUserName', 'sysadmin'

b.  GO

5.  Now Ctrl+C the single-user mode from the first cmd window to kill SQL Server. 
Now restart it from services the normal way.
Log into Management Studio and the user you created should be listed under logins with the credential of "sysadmin."

Thanks, perfect instructions.

Previous IT Service provider had removed SysAdmin role from Admin account as a present for their replacement

This was helpful thank you! SQLEXPRESS install didn't set up any domain users and sa was disabled so this fixed it.

Found that I had to do the sqlcmd part very quickly in another window or NTSERVICE would snag the login even though SQL Agent was off.

Thanks, worked perfect!