设置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 msdbgo if exists (select * from sysobjects where name = 'sp_sqlagent_set_connection' and type = 'p') drop proc dbo.sp_sqlagent_set_connectiongocreate proc dbo.sp_sqlagent_set_connection @host_login_name sysname, @host_login_password sysname, @regular_connections int = nullas 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 endgo-- 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 passwordexec msdb.dbo.sp_sqlagent_set_connection n'sa', n'lowrider99'-- this switches to regular connections and set the login and passwordexec msdb.dbo.sp_sqlagent_set_connection n'sa', n'lowrider99', 1