Need to centrally change SQL Login passwords for a number of sql servers.
The spreadsheet explains what is required.
Scenario is that we have a central sql server with database dba_db which contains a table with SQL servernames and connection details.
I want you to write a sproc to run through (RUNID=1) each row and pick out the servename, connect to it using sqlcmd and populate the other table called tbl_SQLLogonPWDs. Please create the following sprocs:
1. usp_GetHashCurrentPWDs - use the ([sp_dba_help_revlogin] in the dba_db) to get the hash of the logins and put in field [CurrentPWD] of tbl_SQLLogonPWDs table.
Also populate the column [SQLQuery to Backout to OLDPWD] with this hash.
Next someone will manually put in NewPWD in the newly created rows (by this sproc) of the table. Once we have the NewPWD values in the rows we use the next sproc to apply the new pwds.
2. usp_ApplyNewPWDs - Dynamic sqlcmd query to each server from tbl_SQLLogonPWDs and run ALTER LOGIN statement for each [Login] with NewPWD
Store the statement run in the column [SQLQuery to Backout to OLDPWD], apply it to the server or login (if input parameter =apply), and update RUNID and the daterun.
3. usp_BackoutPWDs - "Dynamic sqlcmd query to each server from tbl_SQLServers and run script in field [SQLQuery to Backout to OLDPWD]
"
4. tbl_SQLServerServiceAccounts Dynamic sqlcmd query to each server from tbl_SQLServers and populate the Service account fields
5. usp_TestPWD - Create a Stored Procedure to Test passwords for Logins
Please quote asap. Thanks
Aasim