Hi I am currently performing the following tasks manually and was wondering
if anyone might suggest if a script,dts or possibly .net application could do
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
--
Paul G
Software engineer.More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>sql
2012年3月27日星期二
anyone know the best way to do this
Hi I am currently performing the following tasks manually and was wondering
if anyone might suggest if a script,dts or possibly .net application could d
o
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
--
Paul G
Software engineer.More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>
if anyone might suggest if a script,dts or possibly .net application could d
o
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
--
Paul G
Software engineer.More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>
anyone know the best way to do this
Hi I am currently performing the following tasks manually and was wondering
if anyone might suggest if a script,dts or possibly .net application could do
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
Paul G
Software engineer.
More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.
|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>
|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.
|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>
if anyone might suggest if a script,dts or possibly .net application could do
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
Paul G
Software engineer.
More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.
|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>
|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.
|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>
2012年2月13日星期一
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
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
订阅：
博文 (Atom)