设置SQL的Agent代理的登陆名和密码问题

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

-- Copyright (C) 1991-2002 SQLDev.Net
-- 
-- file: sp_sqlagent_set_connection.sql
-- descr.: Set login and password for regular connections to SQL Agent
-- author: Gert E.R. Drapers ([email?protected])
--
-- @@bof_revsion_marker
-- revision history
-- yyyy/mm/dd  by       description
-- ==========  =======  ==========================================================
-- 2003/03/20  gertd v1.0.0.0 first release
-- 
-- @@eof_revsion_marker
-- ***************************************************************************
use msdb
go 

if exists (select * from sysobjects where name = 'sp_sqlagent_set_connection' and type = 'P')
 drop proc dbo.sp_sqlagent_set_connection
go

create proc dbo.sp_sqlagent_set_connection @host_login_name sysname,@host_login_password sysname,@regular_connections int = NULL
as
 set nocount on

 declare @rc int,@os int

 -- check if sysadmin role member
 if is_srvrolemember ('sysadmin') <> 1
 begin
  raiserror('Only members of the sysadmin role can execute sp_sqlagent_set_connection',16,1)
  return
 end

 -- check parameters
 if (@host_login_name is null) or (len(@host_login_name) = 0)
 begin
  raiserror('Illegal parameter value %s is NULL or empty',1,'@host_login_name')
  return
 end

 if (@host_login_password is null) or (len(@host_login_password) = 0)
 begin
  raiserror('Illegal parameter value %s is NULL or empty','@host_login_password')
  return
 end
 
 -- check if SQL Server 2000,depends on master.dbo.xp_sqlagent_param
 if (charindex(N'8.00',@@version,0) = 0)
 begin
  raiserror('sp_sqlagent_set_connection is not supported for versions earlier than SQL Server 2000',18,1)
  return
 end

 -- check OS,master.dbo.xp_sqlagent_param only works on NT
 exec @rc = master.dbo.xp_MSplatform @os output
 if (@os = 2) -- Windows 9x
 begin
  raiserror('sp_sqlagent_set_connection is not supported on Windows 95/98 platforms',1)
  return
 end
 
 -- only if @regular_connections is turned on we allow setting the connection,otherwise we delete it
 if (@regular_connections is null)
 begin
  exec @rc = master.dbo.xp_instance_regread 
   N'HKEY_LOCAL_MACHINE',N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent',N'RegularConnections',@regular_connections OUTPUT,N'no_output'
 end
 else
 begin
  exec @rc = master.dbo.xp_instance_regwrite 
   N'HKEY_LOCAL_MACHINE',N'REG_DWORD',@regular_connections
 end

 -- delete user id and password
 if (@regular_connections = 0)
 begin
  print N'Delete HostLoginID'
  exec @rc = master.dbo.xp_sqlagent_param 2,N'HostLoginID'
  print N'Delete HostPassword'
  exec @rc = master.dbo.xp_sqlagent_param 2,N'HostPassword'
 end
 
 -- set user id and password
 if (@regular_connections = 1)
 begin
  print N'Set HostLoginID'
  exec @rc = master.dbo.xp_sqlagent_param 1,N'HostLoginID',@host_login_name
  print N'Set HostPassword'
  exec @rc = master.dbo.xp_sqlagent_param 3,N'HostPassword',@host_login_password
 end
go

-- sample usage

-- regular_connections is already turned on either using SQL Enterprise Manager or 
-- exec msdb.dbo.sp_set_sqlagent_properties @regular_connections = 1
-- this sets the login and password
exec msdb.dbo.sp_sqlagent_set_connection N'sa',N'LowRider99'

-- this switches to regular connections and set the login and password
exec msdb.dbo.sp_sqlagent_set_connection N'sa',N'LowRider99',1

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：常州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!