Start SQL Server Express in single user mode and add your user to the sysadmin role – access a database for which you don’t have permission

If your Windows user account doesn’t have access to a database, add the account to the sysadmin role to get permission to access all databases on the server.

https://support.microsoft.com/en-us/help/937682/how-to-add-a-windows-user-to-the-sysadmin-fixed-server-role-in-sql-ser

First, stop the SQL Express service.

The one thing the article omits (I don’t see it there) is the bold part:

C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Binn> .\sqlservr.exe -sSQLEXPRESS -m”SQLCMD” -c

That allows a client with the name “SQLCMD” to connect. If you omit that part and just put -m, you won’t be able to connect:

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user ‘DOMAIN\user’. Reason: Server is in single user mode. Only one administrator can connect at this time..

Once you’ve started the server in single user mode, open another window and run:

SQLCMD -S .\SQLEXPRESS

to connect to the SQL Express instance on the local machine.

Then run

sp_addsrvrolemember ‘DOMAIN\user’, ‘sysadmin’
go

Then press Control-C to close sqlcmd and single user mode.

Advertisements

Blog at WordPress.com.

Up ↑