Disaster Recovery: How to regain your lost sysadmin access

  • 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.com
  • 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

  • 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.

  • 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

  • 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!

  • 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

  • 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.com
  • Performed this months ago. It works fine & the technique is quite familiar these days.


    Sujeet Singh

  • 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.

  • 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!

  • 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.

  • 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.com
  • 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.

  • This nice trick saved my butt in the past. Thanks for writing it down for us in SSC 😉

  • 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