What to do when you are locked out of SQL Server

Tweet

I was recently helping out a fellow Headspringer when we realized we were locked out of his local SQL Express installation. We just needed to start SQL Server in single-user mode, give rights to whatever users we want, and then start back in normal mode. Here’s what we did:

1. Launch SQL Server Configuration Manager (Run -> SQLServerManager11.msc)
   
   
2. Stop SQL Server (SQLEXPRESS) instance
   (To stop: right-click stop or click then click the stop icon)
   
   
3. Go to the properties of SQL Server
   (To get to properties: right-click click properties or click then press alt+enter)
   
   
4. SQL Server 2012
   Click “Startup Parameters” tab and enter “–m” then press Add, Apply, and close the window
   
   
   SQL Server 2008/2005 Unfortunately I don’t have a screenshot of the 2008 or 2005 properties window, but I believe the startup parameters is under the advanced tab and make sure to add separate the parameters by ; (this bit me the first time around)
   
5. Start the SQL Server (SQLEXPRESS) service
   (To start: right-click and click Start or click then click the start icon)
   
6. Run SQL Server Management Studio
   (To start: win+r and type ssms press enter)
   
   
7. Connect and add the BUILTIN\Administrators or whatever users you prefer to add
   
8. Remove the –m startup parameter
1. Stop SQL Server
2. Open Properties
3. Remove Startup Parameter
4. Start SQL Server
5. Good to go

View more at microsoft.com