Locked out of SQL...No More
I had an interesting issue come up at a customer where they needed to change SQL Memory and reattach a database, neither action they could accomplish. For some reason, they had no rights to do so (and should have).
How to remedy this? Before doing this, you will need the SQL Server, Instance name (I'll provide examples for the default instance, just in case), and user account and domain that needs permissions. For this example, I will assume the following:
- Domain: Local
- User Account: joeking
- SQL Server: Server1
- Instance Name: SQLExpress
Now, perform the following steps from an Administrator Command Prompt:
1. Stop the SQL Server Service
For the example named instance:
net stop MSSQL$SQLExpress
For the default instance:
net stop MSSQLServer
(You may be prompted about dependent services like SQL Server Agent)
2. Start the SQL Server Service in single user mode:
For the example named instance:
net start MSSQL$SQLExpress /m
For the default instance:
net start MSSQLServer /m
3. Connect to the SQL Server
Type the following command for the named instance:
sqlcmd -S Server1\SQLExpress
For the default instance:
sqlcmd -S Server1
If it all worked, you'll see a "1>" prompt. You can then start typing commands.
4. Add the user to the sysadmins group
At the "1>" prompt, type the following:
sp_addsrvrolemember 'Local\joeking','sysadmin'
(and press Enter), you'll see a "2>" prompt. Type:
GO
(and press Enter). You won't see any output if the command succeeded.
5. Stop the SQL Server Service
At the command prompt, type the following:
For the example named instance:
net stop MSSQL$SQLExpress
For the default instance:
net stop MSSQLServer
6. Restart the SQL Server Service
For the example named instance:
net start MSSQL$SQLExpress
For the default instance:
net start MSSQLServer
From here, you should be able to log back into SQL Management Console and do what you need to do with the user you added.
— If you like my blogs, please share it on social media, rate it, and/or leave a comment. —