January 5, 2012 at 7:29 am
I have an instance of SQL Server 2005 (9.00.3042.00) which I would like to patch up to Service Pack 3 but I do not know the sa password.
I can not find where any of the Applications hitting the 20 some-odd Databases are using the sa password to connect so my plan was to just change the 'sa' password before running the Service Pack 3 executable. I have all of my logins scripted out using the sp_help_revlogin stored procedure - but this does not script out the 'sa' account.
Seeing as none of the Applications are using the 'sa' account for connectivity to the Databases - is there anything else I have to be aware of when I change the 'sa' password?
Any other problems I need to be aware of?
I did attempt to 'script out' the sa login by going into SSMS and right-clicking on the 'sa' password and choosing 'script login as' 'create to' 'new query window' and I get this?
/****** Object: Login [sa] Script Date: 01/05/2012 09:14:10 ******/
/* For security reasons the login is created disabled and with a random password. */
/****** Object: Login [sa] Script Date: 01/05/2012 09:14:10 ******/
CREATE LOGIN [sa] WITH PASSWORD=', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
EXEC sys.sp_addsrvrolemember @loginame = N'sa', @rolename = N'sysadmin'
GO
--ALTER LOGIN [sa] DISABLE
Does anyone know if this is a way if this is a good way to document the 'sa' password?
In other words, can I change my 'sa' password to something I know, apply SP3 and if I needed to change 'sa' back to it's original password for some reason, could I safely drop 'sa' and recreate it with the above script?
January 5, 2012 at 8:35 am
in short - no.
BUT - you don't need to know the sa password to apply a service pack. Just make sure you have sysadmin and the account you run the upgrade under has local admin on the server.
BTW - SP4 is out for SQL2005, any reason you cannot go to that SP?
---------------------------------------------------------------------
January 5, 2012 at 9:15 am
Thank you. I guess not knowing the 'sa' password is not a deal breaker for applying the service pack.
The reason that I am only patching up to SP3 is that I am just trying to get a DEV environment patched up to where the Stage and PROD environments are to level the playing field. the next step would be to get all three (Dev, stage and prod) on SP4
Thanks for your help and advice!
January 5, 2012 at 9:32 am
no probs,
As none of your apps connect with sa there would be no problems with changing the sa password to a known value.
I would not however disable or rename the sa id before an upgrade.
---------------------------------------------------------------------
January 5, 2012 at 10:42 am
I would make sure you socialize this to clients and your help desk/support. If there is something using "sa" it will break after the update if you change the sa password, so check that first.
If you don't know the password, I'd change it just to be aware of what's using it and to document who/what has sysadmin access.
January 5, 2012 at 10:47 am
Thanks all. now that SP3 has been successfully applied. I will go ahead and change 'sa' to a known password.. Thank you all for your help and advice
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply