Add sysadmin role to a user in Microsoft SQL Express

  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 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.