Post reply

Script to Change Standard SQL Account Passwords

  • May 10, 2004 at 3:40 pm

    #82581

    We are now required to change standard sql account passwords every 45 days.

    Does anyone know if a script or dts package exists that will change all of the standard sql account passwords to a randomly generated password and collect the new passwords for the DBAs so they can be disseminated to the clients?

  • May 10, 2004 at 11:50 pm

    #505904

    One way to do this is to get all the SQL Server Logins and create a T-SQL Script that uses the sp_password on each Login to change the password using an alogorithm!

    As for automating it to happen every 45 days, you can put this into a DTS Package. What you have to keep in mind is that how do you document what the new password is and how do you let the users know the ne password?

    Have you checked out any possible 3rd party products for this? I do not know of any yet.

    Kindest Regards,

  • May 12, 2004 at 2:49 am

    #506090

    also you can put it on a job


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • May 12, 2004 at 8:32 am

    #506156

    Perhaps saomething like:

    /* Ckange every users password to random number */

    /* (c) Karl Klingler 26.03.2004 */

    -- Hier muss die richtige Datenbank angewählt werden!!!

    use master

    -- Variablen deklarieren

    declare @seed smallint;

    declare @pwd sysname;

    declare @pwd1 sysname;

    declare @DBN sysname;

    declare @lname sysname;

    DECLARE @rc int;

    -- Cursor für die User deklarieren

    declare users_cur cursor

    for select [name] from [sysxlogins]

    -- und los geht's

    OPEN users_cur

    FETCH NEXT FROM users_cur

    INTO @lname

    WHILE @@FETCH_STATUS = 0 BEGIN

    -- Login anlegen

    select @rc=0

    select @pwd1=str(rand(datename(ms,getdate()))*1000000000);

    select @pwd=substring(@pwd1,4,len(@pwd1));

    exec @rc = sp_password NULL, @pwd, @lname;

    if @rc = 0

    PRINT 'PW for User ''' + @lname + ''' is: "'+@pwd+'".'

    else

    PRINT 'PW for User ''' + @lname + ''' could not be changed.'

    WAITFOR DELAY '000:00:00.01'

    FETCH NEXT FROM users_cur

    INTO @lname

    END

    CLOSE users_cur

    DEALLOCATE users_cur

    GO

    Best regards
    karl

  • May 12, 2004 at 9:26 am

    #506165

    Thank you so very much.  This will work wonderfully.

    Lloyd

  • May 12, 2004 at 4:53 pm

    #506219

    I just submitted a VBScript to the Scripts Library which will do this as well.  It is titled, Generate Strong Passwords for Standard SQL Logins, and should be available as soon as it passes the review process.

    Cheers,

    Steve


    Regards,
    Steve

    Meddle not in the affairs of dragons, for you are crunchy and taste good with ketchup.

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

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