Add sysadmin role to a user in Microsoft SQL Express

Posted on 08-Apr-2022 by krylon
  1. Stop SQLExpress service
  2. Run Command Prompt with administrator rights
  3. Find your install folder, for example
    C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Binn
  4. Start SQL Express in single-user mode: 
    sqlservr.exe -sSQLEXPRESS -m”SQLCMD” -c
  5. Open another Command Prompt window (with administrator rights) and connect to the running instance of SQL Express: 
    SQLCMD -S .\SQLEXPRESS
  6. Run three commands, modifying the first as appropriate for your user to grant them the desired role: 
    sp_addsrvrolemember 'DOMAIN\user', 'sysadmin'

go

quit
  7. Go back to the running instance and kill it with Control-C
  8. Start SQLExpress service
  9. The user specified above should now have sysadmin Server Role
This entry was posted in Uncategorized. Bookmark the permalink.