Script to Change Standard SQL Account Passwords

Question

Script to Change Standard SQL Account Passwords

hll

SSC-Addicted

Points: 470
More actions
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?

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

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

TriggerMe SSChampion Points: 11752 More actions · Answer 1

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,

Alamir SSCertifiable Points: 6085 More actions · Answer 2

also you can put it on a job

Alamir Mohamed
Alamir_mohamed@yahoo.com

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 3

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

hll SSC-Addicted Points: 470 More actions · Answer 4

Thank you so very much. This will work wonderfully.

Lloyd

sharrell SSC-Addicted Points: 477 More actions · Answer 5

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.