2

I am currently facing the problem that someone working here changed the password of the sa user of the Microsoft SQL Server 2005. The SQL-Server is running on a VM, and the application we run won't let me change the password it uses to login. Also, the one who set everything up doesn't work here.

We have only one snapshot of the VM, and that was after everything has been set up, which is some months old, so we can't revert back to it.

Can I use this this snapshot, and copy just the users from the old snapshot to the new one? I looked around in the SQL Server Manager, but there doesn't seem to be a users table.

Thanks in advance, brot

Improve this question

1 Answer 1

Reset to default
2

First, I haven't ever used a VM snapshot to "restore" a SQL Server environment so I don't even know if that is a possibility. If you are asking this, you probably don't have SQL Server backup of master.

If you can use your VM to recreate your SQL Server environment, you could use the sp_help_revlogin system store procedure to generate the hashed password of sa and use that generated password to reset the one needing to be reset.

The editorial comment is at some point in time, you need to lock down who is in the sysadmin SQL Server role or plan on taking frequent backups.

Improve this answer
3
  • well, the VM snapshot is simply the state of the whole system (including windows, the server, etc) at the time you press the "create snapshot" button. Can you give some more detail on the "sp_help_revlogin" stored procedure and how to restore the hash on the current state? Commented Nov 15, 2010 at 14:44
  • All I'm saying is that I haven't seen it done with exclusively a VM snapshot (maybe I need to get out more often :-) ). Commented Nov 15, 2010 at 14:50
  • Thanks to the answer from jl, I got the right keywords to find the solution on the microsoft knowledge-base I can now start the snapshot of the VM, start the SQL Server Management Studio and create the sp_help_revlogin stored procedure. Executing sp_help_revlogin will then output a SQL script. After that, I go from the snapshot to the current state of the VM and execute the script sp_help_revlogin created. Thanks again jl, I will upvote your answer as soon as I have enough reputation. ;) Commented Nov 15, 2010 at 14:59

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.