February 20, 2012 at 8:55 pm
Comments posted to this topic are about the item Disaster Recovery: How to regain your lost sysadmin access
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comFebruary 21, 2012 at 12:58 am
I had to use this method once, but I still couldn't log in as I always got the error that someone was already logged in (and this was not the case).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 21, 2012 at 1:06 am
Koen Verbeeck (2/21/2012)
I had to use this method once, but I still couldn't log in as I always got the error that someone was already logged in (and this was not the case).
You weren't using SQL Management Studio to try and connect, were you? That has an annoying habit of opening multiple logins, which doesn't work terribly well when SQL Server is running in single-user mode--this is why the article recommends using SQLCMD, I would imagine.
February 21, 2012 at 1:09 am
paul.knibbs (2/21/2012)
Koen Verbeeck (2/21/2012)
I had to use this method once, but I still couldn't log in as I always got the error that someone was already logged in (and this was not the case).You weren't using SQL Management Studio to try and connect, were you? That has an annoying habit of opening multiple logins, which doesn't work terribly well when SQL Server is running in single-user mode--this is why the article recommends using SQLCMD, I would imagine.
I've read about that somewhere, and I did try SQLCMD but that threw the same error as well. I probably did something wrong 😀
Out of pure frustration I uninstalled SQL Server and installed it right back. Luckily it was my local test instance on my own laptop 😀 (and I did had back-ups! :w00t:)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 21, 2012 at 1:11 am
Weird. I *have* had to do this a couple of times myself and it's worked fine...no idea why it didn't for you!
February 21, 2012 at 1:12 am
paul.knibbs (2/21/2012)
Weird. I *have* had to do this a couple of times myself and it's worked fine...no idea why it didn't for you!
Beats me either. Like I said, I probably did something wrong 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 21, 2012 at 1:22 am
Documented procedure worked for me as I recovered my access couple of times using this procedure.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comFebruary 21, 2012 at 2:52 am
Performed this months ago. It works fine & the technique is quite familiar these days.
February 21, 2012 at 4:22 am
Smashing bit of info for a newbie like me to file away against the day of disaster.
I notice people saying they can't log on like this, (Single user mode.) At the risk of commenting above my current low skill set, the 432 exam does state that you need to stop the SQL Agent before you sign on in single user mode, or it will grab the allowed single user.
Hope that helps, or isn't too dumb.
February 21, 2012 at 4:26 am
peterob1 (2/21/2012)you need to stop the SQL Agent before you sign on in single user mode, or it will grab the allowed single user.
That would certainly make sense in my case--the SQL servers I've had to do this on have all been Express installs, so they haven't got the SQL Agent trying to interfere!
February 21, 2012 at 8:11 am
Would this work on a SQL 2000 instance? I have a dozen or so that I need to gain access to in order to sunset them later this year. No idea who the owners are, nor any way to find out.
February 21, 2012 at 8:36 am
mike-858099 (2/21/2012)
Would this work on a SQL 2000 instance? I have a dozen or so that I need to gain access to in order to sunset them later this year. No idea who the owners are, nor any way to find out.
Hi Mike,
Yes this procedure works well with all versions of SQL Server. I have used this on to gain access on one of the production SQL 2000 Server to which I haven't got access to.
Basit
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comFebruary 21, 2012 at 9:38 am
peterob1 (2/21/2012)
Smashing bit of info for a newbie like me to file away against the day of disaster.I notice people saying they can't log on like this, (Single user mode.) At the risk of commenting above my current low skill set, the 432 exam does state that you need to stop the SQL Agent before you sign on in single user mode, or it will grab the allowed single user.
Hope that helps, or isn't too dumb.
Not dumb at all.
I've seen well-seasoned pros forget simple things like this.
It's easy to do, especially in a panicked moment when trying to recover as quickly as possible.
February 22, 2012 at 8:14 am
This nice trick saved my butt in the past. Thanks for writing it down for us in SSC 😉
February 22, 2012 at 8:27 am
Since I am now a veteran of having done this to myself a few times, there are times when this happens when I cannot bump off my users which would be the case in "Single User Mode", also you may find an application that automatically connects, thus not allowing you to connect. In a situation as this, one alternate method on a SQL 2005 box is to add your account to the global group "<servername\instance>\SQLServer2005MSSQLUsers$<Servername>$<instance>. This group automatically has system admin rights.
You can also log on using the service account if you happen to be using a domain account and if you are allowed to log onto the server using the service account. This is not typically allowed due to security levels.
Unfortunately for SQL 2008 this group is no longer available.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply