any automated way of changing pwds manually ?

We have a sql authenticated login that we would like to change pwd through a
script and at the same time , let us know what the pwd is in a text file
that would be located at some share. We need to change pwd every week or 2
for security purposes.. Can we have a job that we can run every 2 weeks and
at the same time, write the new pwd to a text file.. It can be a random
complex pwd... Doesnt matter.. Thanks..Using SQL 2000
hi Hassan
"Hassan" <fatima_ja@.hotmail.com> ha scritto nel messaggio
news:%23NV38FZXEHA.952@.TK2MSFTNGP10.phx.gbl...
> We have a sql authenticated login that we would like to change pwd through
a
> script and at the same time , let us know what the pwd is in a text file
> that would be located at some share. We need to change pwd every week or 2
> for security purposes.. Can we have a job that we can run every 2 weeks
and
> at the same time, write the new pwd to a text file.. It can be a random
> complex pwd... Doesnt matter.. Thanks..Using SQL 2000
>
you can write a Transact-SQL job like
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name =
N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'[Uncategorized (Local)]'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'change pwd') > 0
PRINT N'The job "change pwd" already exists so will not be replaced.'
ELSE
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'change pwd', @.owner_login_name = N'sa', @.description = N'No
description available.', @.category_name = N'[Uncategorized (Local)]',
@.enabled = 1, @.notify_level_email = 0, @.notify_level_page = 0,
@.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
1, @.step_name = N'change PWD', @.command = N'DECLARE @.newPwd VARCHAR(128)
SELECT @.newPwd = CONVERT(VARCHAR(20) , CONVERT ( INT, getdate()))
SELECT @.newPwd = USER_NAME() + @.newPwd + CONVERT(VARCHAR(20) , RAND(CONVERT
( INT, getdate())))
SELECT @.newPwd = @.newPwd + REVERSE ( @.newPwd )
SELECT @.newPwd AS [NEW PASSWORD]
EXEC sp_password @.new = @.newPwd
, @.loginame = ''roby''
', @.database_name = N'master', @.server = N'', @.database_user_name = N'',
@.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts
= 0, @.retry_interval = 1, @.output_file_name = N'c:\newpwd.txt',
@.on_success_step_id = 0, @.on_success_action = 1, @.on_fail_step_id = 0,
@.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
which writes to c:\newpwd.txt' file the new generated password... the logic
for password creation is up to you =;-D
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi Hassan
"Hassan" <fatima_ja@.hotmail.com> ha scritto nel messaggio
news:%23NV38FZXEHA.952@.TK2MSFTNGP10.phx.gbl...
> We have a sql authenticated login that we would like to change pwd through
a
> script and at the same time , let us know what the pwd is in a text file
> that would be located at some share. We need to change pwd every week or 2
> for security purposes.. Can we have a job that we can run every 2 weeks
and
> at the same time, write the new pwd to a text file.. It can be a random
> complex pwd... Doesnt matter.. Thanks..Using SQL 2000
>
you can write a Transact-SQL job like
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name =
N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'[Uncategorized (Local)]'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'change pwd') > 0
PRINT N'The job "change pwd" already exists so will not be replaced.'
ELSE
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'change pwd', @.owner_login_name = N'sa', @.description = N'No
description available.', @.category_name = N'[Uncategorized (Local)]',
@.enabled = 1, @.notify_level_email = 0, @.notify_level_page = 0,
@.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
1, @.step_name = N'change PWD', @.command = N'DECLARE @.newPwd VARCHAR(128)
SELECT @.newPwd = CONVERT(VARCHAR(20) , CONVERT ( INT, getdate()))
SELECT @.newPwd = USER_NAME() + @.newPwd + CONVERT(VARCHAR(20) , RAND(CONVERT
( INT, getdate())))
SELECT @.newPwd = @.newPwd + REVERSE ( @.newPwd )
SELECT @.newPwd AS [NEW PASSWORD]
EXEC sp_password @.new = @.newPwd
, @.loginame = ''roby''
', @.database_name = N'master', @.server = N'', @.database_user_name = N'',
@.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts
= 0, @.retry_interval = 1, @.output_file_name = N'c:\newpwd.txt',
@.on_success_step_id = 0, @.on_success_action = 1, @.on_fail_step_id = 0,
@.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
which writes to c:\newpwd.txt' file the new generated password... the logic
for password creation is up to you =;-D
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply