设置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 登陆名 密码 <style> .CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;} .CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;} .CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left本文来源gao@dai!ma.com搞$代^码!网7:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding: 3px 10px;} .CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;} .CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;} .CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;} </style> <!—ecms -ecms

–> <!—ecms -ecms
• $velocityCount

–> <!—ecms -ecms

–>

-- 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 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

喜欢 (0)赏

[搞代码]

分享 (0)