Wednesday, May 15, 2013

From 0 to sysadmin in 30 seconds...

This is a quick tip for anyone put on the spot like I was today...

Picture the scene.  It's five to five, I'm packing up my laptop and finding my headphones.  I'm about to leave the office.  In a hurry, one of our engineers comes over and explains how the on-site supplier is having a problem accessing an obscure, unsupported SQL Server database.  They can't get access.

So I go over to have a look.  It's a SQL Server 2005 DB on the application server.  I've never seen it before.  The engineer explains how they have a username, but the password is lost.  SQL Server Management Studio isn't installed.  No-one knows how to retrieve or reset the password for the user they know of.

All faces turn to you.  What do you do?

In summary:

Start -> Run -> cmd
sqlcmd -Slocalhost -E
(if you're lucky, you'll get in, as 2005 has local admins as sysadmins by default).
If this doesn't work - sqlcmd -Slocalhost -E -A

You're in.  If you're not, get local admin access on the machine first.  Now...

create login 'me' with password 'Pass1234';
exec sp_addsrvrolemember @loginame = 'me', @rolename = 'sysadmin';
sqlcmd -Slocalhost -Ume -PPass1234

You're now sysadmin.

alter login 'someLogin' with password = 'new password';
(this is the username they've supplied.  Create a new password).

Job done.  Coat on, go home.