SQLCMD | SQL Professional

Getting an error like this after you start SQL in single user mode can be frustrating:

This happens to me sometimes when I am attempting to reset passwords of instances. Here are the steps you can take to get by this error (“Run as Administrator” should be selected for all command below) :

Stop the SQL Server Service:

net stop MSSQLSERVER

1

net stop MSSQLSERVER
Start the SQL Server Service using the switches below. The service will not fully start and the “service is starting…” message will remain in the command window. Once this message is displayed you can move on to step 3:

net start MSSQLSERVER /c /m /T3608

1

net start MSSQLSERVER /c /m /T3608
Either open Management Studio and connect to the instance, or open a new CMD window to use SQLCMD.
Once you’ve completed the necessary changes the SQL Server Service will need to be forcibly stopped. Either end the process in task manager or use the following command in the windows CMD window:

taskkill /IM sqlservr.exe /F

1

taskkill /IM sqlservr.exe /F
Now SQL can be started normally.

Scenario: I’ve recently came across a user that had SQL on a server. The user was on the instance, but they had limited permissions. In essence this created a user that could login the SQL engine but had no access to the databases or the ability to add permissions to their account even though the account was a local administrator.

Here are the steps I took to elevate the user’s privileges:

Start the instance in Single User Mode:

How to Start SQL in Single User Mode

Grant user new permissions:

EXEC master..sp_addsrvrolemember @loginame = N'test', @rolename = N'sysadmin'
GO

1 2	EXEC master..sp_addsrvrolemember @loginame = N'test', @rolename = N'sysadmin' GO

Restart SQL in “normal” mode and login through Management Studio

It’s that simple

**I feel it is important to note that the user performing the SQLCMD query must be a local administrator.

SQL Professional

SQL Server with Geoff Pasley

Tag Archives: SQLCMD

Elevate User Permissions