SysAdmins Locked Out of SQL Server
-
Brian, all that would certainly work, and I'll remember this thread for if I'm ever in a similar circumstance again. As it is I'm playing buttoutsky on this one, for political and sociological reasons. (Certainly there has been no talk about rolling anything back... and I want nothing to do with the rope gathering crew.)
Anyway, I have had success recovering the one SQL Server instance, and this methodology should work most anywhere (SQL Server 2000, SP is probably irrelevant).
A key time saver: immediately after my last post, I recalled reading once about the “rebuild master database” utility (on SQL Server Central, naturally). So:
-- stopped the “lost” [default] instance of SQL Server, made copies of the system databases (master, msdb, model)
-- ran rebuildm.exe. It overwrote the system database files (which is why I made copies), and—-most impressive—-bumped them to SP4. Good work by someone in Microsoft there!
-- Made copies of these new system database files. (Didn’t need them, but you never know)
-- Checked to make sure SQL worked—it did, looked just like a new instance.
-- Stopped SQL, overworte the "new" msdb database files with copies of the old, started SQL, and…
-- Success! All the old msdb data was there. A quick check turned up some jobs owned by NT logins; here, the fix is to make them owned by SA, but you may need to set them to the appropriate "new" NT logins. (I also noticed that our few DTS packages are now owned by old domain and thus invalid NT logins. No clue if this matters a whit...)
-- Next, attempted to sp_attach the old master database files as database “foo”. Much to my surprise, this worked. (I’d have thought there’d be a hidden bit in these--and only these--database files to keep someone from hacking things this way…)
-- You’ll note I didn’t say “made a copy of my old system database files before hacking them”. Oops. Fortunately didn’t matter here, but if you ever do something like this make those copies!
-- Had to use sp_configure with ‘allow updates’, as even though it's not a system database, sysxLogins is tagged as a system table (object).
-- Spent far too much time trying to update the password in the SA entry, copy over the current SA login, reload “BUILTIN\Administrators” in the table, update the access rights [xstatus&16] and reset the password of an old sql authenticated login. This involved lots of stopping and copying of copies of database files. Feh.
-- Eventually recalled the lesson of SP3, went “duh”, and just set the SA password in “foo” (old master database) to null.
-- Copied the files over to be the live master database files, and Shazam! I can now log back into the essentially fully restored SQL Server instance as SA, using an empty password!
-- Very next thing I did: set that SA password!
And so the story has a happy if convoluted ending. (With all that messy master database file copying and hacking of locked system tables, you’ll now know why I refer to this stuff as “brain surgery” and “brain transplants”. I’d rave a bit like Dr. Frankenstein, but no one here would get it.)
Now I wonder if my email's working yet...
Philip
-
You know, when all is said and done, you should write this up as an article and submit it to Steve.... names omitted or changed to protect the innocent, of course.
K. Brian Kelley
@kbriankelley -
Rave away Philip, that was intersting reading. Please excuse me tho when I say rather you than me!
Max
-
Hi Philip!
I Agree with Max and Brian! That's a hell of a job you did there! I really hope you publish this on SSC.
JP
-
Thanks for the positive feedback! I'll give it some thought--but, as you might imagine, just now I don't have all that much free time. To do the subject justice, I'd have to go back and do some more testing, get some screen shots, maybe mess around with the model database... it'd add up, and I dislike doing things only half-way.
Philip
-
Followup #1: Had to apply this process to 6 developer boxes, so everything became a bit more formalized. Still some bugaboos I haven't figure out, but the process works.
Followup #2: I am an idiot--a fact I only realized the weekend after all the above happened. All that has to be done is to copy the master database files to another SQL-prepped computer, attach the DB, change the SA password to null, copy the files back over the original files [keeping extra copies ov everything just in case], and proceed. Cuts RebuildM right out of the circuit.
If I write an article, the ultimate point might be to take time to think a problem through, don't just dive right into writing code and running procedures. (Everyone knows that, but how often do you remember it?)
Philip
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply