April 20, 2010 at 3:20 am
Hi,
I plan to change SA passwords on SQL Server 2000 and SQL Server 2005. Please let me know if it is possible to backup the password, so in case of any problems I can go back to previous one? Of course I mean those that I do not know 🙂
April 20, 2010 at 3:32 am
run this on each server and it will create an alter login script with the old password
select 'alter login [sa] with password = ' +
sys.fn_varbintohexstr(password_hash) +
' hashed' + ' --Login from Server = ' + @@servername
from sys.sql_logins
where name = 'sa'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 20, 2010 at 5:28 am
that is for both, sql 2005 and 2000?
I have just tried it on SQL Server 2005. It generate nice query
alter login [sa] with password = <hashed password> hashed --Login from Server = <server name>
then I changed sa password manualy and try to run the query in order to get back old password but it did not work. I got error:
Incorrect syntax near '<hashed password>'
It does not work only for hashed password, I tried to change non-hashed and it worked fine...
any suggestions what went wrong?
April 20, 2010 at 12:42 pm
sorry my bad that is only for SQL 2005 onwards
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 21, 2010 at 3:58 am
Save output of following query and go for password change for sa.
Query 1:
select password,name from master.dbo.sysxlogins
where name='sa'
In case, u need to reset it use the below mentioned code(All at ur own risk as system table update is involved and is not recommended)
sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
--Below mentioned code wll set old password for sa
update sysxlogins
set password=output of Query 1
where name='sa'
sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
Finally, test if all is fine.
MJ
April 21, 2010 at 1:44 pm
Did you get a chance to try wht I suggested?
MJ
April 21, 2010 at 4:32 pm
A couple of questions
1) Why would you want to backup the sa password? If something went wrong, why would you just not change it back?
2) If something we wrong during the change, and I can't think what would, why would you just not try to connect using the old password?
While some poorly written older apps may connect using sa, backing up the password would really no accomplish much.
If you have NT Authentication enabled (and I have not seen a sever 7.0 or later) that did not have NT authentication enabled, why would this be a concern, you can always connect via NT (and should always connect via NT is SSMS)
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 23, 2010 at 2:43 am
hi Manu-j,
I did not try your solution because as you said it is not recommented to change the system tables. If I do not have any other choice then I will go for it.
I could finnaly run the query from Perry, I found that it works when you install SP2 on SQL Server 2005. It is not a solution for SQL Server 2000, so those servers I will try your suggestion. However I will start with those 2005.
Thank you for Manu-j for your help!
August 25, 2011 at 12:43 pm
I tried it and it worked like a champ
Thanks a ton....
--Vamsi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply