Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Disaster Recovery: How to regain your lost sysadmin access


Disaster Recovery: How to regain your lost sysadmin access

Author
Message
Basit Farooq
Basit Farooq
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 812
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
Koen Verbeeck
Koen Verbeeck
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24451 Visits: 13268
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).


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2335 Visits: 6230
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.
Koen Verbeeck
Koen Verbeeck
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24451 Visits: 13268
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 :-D
Out of pure frustration I uninstalled SQL Server and installed it right back. Luckily it was my local test instance on my own laptop :-D (and I did had back-ups! :w00tSmile


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2335 Visits: 6230
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!
Koen Verbeeck
Koen Verbeeck
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24451 Visits: 13268
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 :-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Basit Farooq
Basit Farooq
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 812
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
Divine Flame
Divine Flame
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2117 Visits: 2816
Performed this months ago. It works fine & the technique is quite familiar these days.


Sujeet Singh
peterob1
peterob1
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 18
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.
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2335 Visits: 6230
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search