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

设置SQL的Agent代理的登陆名和密码问题 Agent 登陆名 密码 -- 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 (GertD@

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

-- 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 (GertD@SQLDev.Net)
--
-- @@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', 16, 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', 16, 1, '@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', 18, 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'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
   N'RegularConnections',
   @regular_connections OUTPUT,
   N'no_output'
 end
 else
 begin
  exec @rc = master.dbo.xp_instance_regwrite 
   N'HKEY_LOCAL_MACHINE',
   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
            N'RegularConnections',
            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