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