Run password changes on multiple servers within central management server

  • Hi 

    I have a large sql instance estate with all instances registered withing a central management server.

    I would like to update a coupe of hundred passwords for different sql logins on different sql instances.  

    Apart from connecting to each instance individually and running my specific update login commands for the specified list of users on that particular instance is there any way I can make use of CMS to do this quicker.

    On each instance different logins will be updated with password changes or accounts deactivated or indeed similar logins will be updated but with different passwords. So for example the sa account might be disabled on one sql instance but have the password changed on another. 

    thanks in Advance

  • Greetings,

    Well, you could run your individual scripts on each server as you stated.
    The other option is to run a power shell script, which would connect to each server, and perform the password changes.  I have seen this done before.  I do not have the code for it.  Please check this Web site.

    https://social.technet.microsoft.com/wiki/contents/articles/22767.sql-server-change-the-login-password-using-powershell.aspx

    Cheers

    DBASupport

  • @cyrusbaratt  thanks but this wont work in my situation. There are hundreds of logins to be changed, different logins on different servers and I wouldn't have time to be entering in the parameters of passwords for those on the command line.

    At the moment the quickest way to do this seems to be pre create the scripts in excel and individually run each server script (for required password changes and disabling logins) by connecting to each server 1 at a time in CMS. Note I need to keep a record of each password change also as they will be stored in a secure area.

  • hi,
    what about this solution:
    Query you CMS for the instance names.
    Paste the result into a text-file
    Make a batch file using the text-file as input for a for %%a in textfile do sqlcmd.exe -S%%a ...
    and run sqlcmd with sql script containing the login changes.
    you can also log the output into an extra file.

    And for sure you could also use powershell to do this.

    BR
    Gerald

  • Thanks @gerald72 - Anymore detail on the CMS query or powershell script? I wonder is there one out there anywhere which does similar?

  • Here's a quick and dirty method.  Provided you know in advance the names of the servers and logins and the new passwords, you could use Excel to generate a script like this - one line for each login.  You could then use CMS to connect to all servers and run the whole script.
    IF @@SERVERNAME = 'SERVER1' EXEC ('ALTER LOGIN Login1 WITH PASSWORD = ''MyNewPassword''')

    John

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply