Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

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.

share|improve this answer
    
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? –  brot Nov 15 '10 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 :-) ). –  jl. Nov 15 '10 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. ;) –  brot Nov 15 '10 at 14:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.