SQL Server Performance Forum – Threads Archive
2005 alter login SA Confusing result
I am doing the regular SA password chaning for over 200 instance all over the world. I wrote a script for both 2000 and 2005 instance.the idea is to create server_cursor, using xp_cmdshell and osql utility. for the error handling, I used osql -o option to record the SQL Server returned msg to a servername named txt file for tracking if the SA password changed or not. For more than 150 2000 instance, set @sa_changing_str = ‘osql -E -Q "exec master.dbo.sp_password ‘
+ QUOTENAME(@old_sa_pwd, @delimiter)
+ ‘,’ + QUOTENAME(@new_sa_pwd, @delimiter)
+ ‘,’+ ‘sa" -S ‘ + @host_name + ‘ -o C:SApwd’
+ @root_name + ‘.txt’
xp_cmdshell @sa_changing_str I had no problem, at beginning I checked the network connections, SQL Server returned with the "password changed" OR "Old password is not correct" While for more than 50 2005 instance, sa_str= ‘osql -E -Q "ALTER LOGIN SA WITH PASSWORD = ‘
+ QUOTENAME(@new_sa_pwd, @delimiter) + ‘ old_password = ‘
+ QUOTENAME(@old_sa_pwd, @delimiter) + ‘ " ‘
+ ‘-S ‘ + @host_name + ‘ -o E:oSApwd’+ @root_name + ‘.txt’ For the same str, some instance even gave syntax error, some server returned "completed the command" when I checked, the SA was changed successful 90% server returned
"Msg 15151, Level 16, State 1,Server UMS190102, Line 1
Cannot alter the login ‘SA’, because it does not exist or you do not have permission." When I check the result, some instance SA was changed, while some server did not change the SA password. Some insight? can Gurus direct me some dependable SPs or whatever with clearly returned messages to get the SA password changed for 2005 instance.
Thanks. New DBA
Can you find whether any of those instances are using LocalSystem account to startup the SQL serives. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
To satya, most instances are using the same domain account for both SQL and SQL Agent services
New DBA
]]>