March 15, 2008 at 4:07 pm
Hi,
Recently some body played with our SQL Server and removed our Sys admin group login and we dont have builtin\admin as login and no SA Password
can any body tell the way to come out with this situation to change the SA Password or create our Group back with Sys admin priviledges.
also to confirm we are still in NT admin group for the server.
below article talk about similar case but as soon as i try to run sqlcmd utility after taking server in single mode as per article ,it throws error that you cant run sqlcmd since server in Single user mode.
Any pointer is appreciated..
March 15, 2008 at 5:10 pm
Hah, this is straight out of the Question of the Day for July 23rd, 2007 that I just answered:
Correct answer:
Start the instance in single user mode and connect as a member of builtin\Administrators to reset the sa password.
Explanation:
In SQL Server 2005, even if you remove builtin\Adminstrators, you can still use this group to access the server if you start the server in Single User mode. This provides you with a disaster recovery backdoor.
Ref: Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005 - http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 16, 2008 at 7:06 am
Yup, and it does work. Had to do that a couple months ago after someone removed builtin\admins without adding the DBA's windows group first. :hehe:
Shut down the SQL service (and make sure nothing's auto starting it). Go to the command prompt and navigate to the dir where the sql executable is. (I'm assuming it's the default instance, named instances are a bit different.)
Run sqlservr.exe -m You'll see all the usual start up messages for sql server.
open another cmd prompt and type sqlcmd -E
It should get you in withn admin privileges.
Just remember single user means just that, single user. If someone gets in before you get sqlcmd connected, then they're the single user.
And may I suggest that you find the bored person and remove their sysadmin rights...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2008 at 10:42 am
I made the same mistake once.
Fortunately, I knew the password of the SQL Startup Service domain account and was able to connect as that acct.
Thanks all, good to know of this technique...
Word of advice:
Keep your sa and other passwords securely stored in a passwd manager tool and make sure they reflect all latest changes.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 16, 2008 at 11:47 pm
Thanks Guys ,but still i am not able to connect after putting the server in single user mode and getting below error ,i tried several times also made sure my connection is the only connectio open..
Msg 18461, Level 14, State 1, Server xxxxxx, Line 1
Login failed for user 'Domain\xxxxx'. Reason: Server is in single user mode.
Only one administrator can connect at this time.
March 17, 2008 at 12:08 am
Is that account in the local administrators group?
Are you sure something else isn't connecting in? SQL Agent, MOM, anything like that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2008 at 10:31 am
Account is in NT admin group , also is there any way to check which user connection is active when SQL comes in single user mode?
Thanks..
March 17, 2008 at 10:47 am
GilaMonster already mentioned it, but here is a slightly expanded version:
Stop and disable all applications that make connections to the instance of the database engine, including Backup Exec (Veritas) remote agent, MOM, SCOM etc
Backup Exec has burned me before... 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 17, 2008 at 11:03 am
Thanks , it looks either MOM or full text was the culprit , it worked now ...
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply