The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_helpdistributor(nvarchar @publisher, nvarchar @local)
MetaData:
-- -- Name: -- sp_helpdistributor -- -- Description: -- Procedure used to obtain distributor information. -- -- Returns: -- 0 == SUCCESS -- 1 == FAILURE -- Several output parameters or result set -- -- Security: -- limited public access -- Requires Certificate signature for catalog access -- -- Notes: -- This is a public stored procedure used to gather general -- distributor information. It can be run on a publisher or -- a subscriber that has a sysservers entry for the distributor. -- -- Four output parameters are accessible with public access: -- -- @distributor Distribution server name -- @distribdb Distribution database -- @rpcsrvname rpc server name -- @publisher_type Publisher type -- -- One output parameter requires PAL access to a publication -- associated with the publisher. -- -- @directory Working directory -- -- The remaining six output parameters require elevated authorization. -- 'sysadmin' has access to all results, from any database, at a server -- with a sysservers entry identifying the distributor. Access is also -- extended to a 'db_owner' running in a publishing database at a -- publisher -- -- @account SQL Server Agent login -- @min_distretention min distribution retention -- @max_distretention max distribution retention -- @history_retention history retention period -- @history_cleanupagent history cleanup agent -- @distrib_cleanupagent distribution cleanup agent -- -- Parameters that the current user is not authorized to access are -- returned as NULLs, both as output parameters and as columns in the -- returned result set. -- create procedure sys.sp_helpdistributor ( @distributor sysname = '%' OUTPUT, -- The distribution server name -- @distribdb sysname = '%' OUTPUT, -- The distribution database -- @directory nvarchar(255) = '%' OUTPUT, -- The working directory -- @account nvarchar(255) = '%' OUTPUT, -- The Windows NT user account -- @min_distretention int = -1 OUTPUT, -- The min distribution retention -- @max_distretention int = -1 OUTPUT, -- The max distribution retention -- @history_retention int = -1 OUTPUT, -- The history retention period -- @history_cleanupagent nvarchar(100) = '%' OUTPUT, -- The history cleanup agent -- @distrib_cleanupagent nvarchar(100) = '%' OUTPUT, -- The distribution cleanup agent -- @publisher sysname = NULL, -- Name of publisher -- @local nvarchar(5) = NULL, -- Get local server values -- @rpcsrvname sysname = '%' OUTPUT, @publisher_type sysname = '%' OUTPUT ) AS BEGIN SET NOCOUNT ON -- -- Declarations. -- DECLARE @loc_distributor sysname DECLARE @loc_distribdb sysname DECLARE @loc_directory nvarchar(255) DECLARE @loc_account nvarchar(255) DECLARE @loc_mindistretention int DECLARE @loc_maxdistretention int DECLARE @loc_historyretention int DECLARE @loc_historycleanupagent nvarchar(100) DECLARE @loc_distribcleanupagent nvarchar(100) DECLARE @loc_security_mode int DECLARE @loc_login sysname DECLARE @loc_password sysname declare @loc_rpcsrvname sysname DECLARE @loc_publishertype sysname DECLARE @proc nvarchar(255) DECLARE @retcode int declare @rpcsrvlogin sysname declare @srvid smallint declare @dist_rpcname sysname declare @platform_nt binary declare @has_dbowner_access bit declare @has_PAL_access bit declare @login sysname select @has_dbowner_access = 1 select @has_PAL_access = 1 select @platform_nt = 0x1 select @login = suser_sname(suser_sid()) -- -- processing for publisher -- IF @publisher IS NULL BEGIN -- -- 6.x compatibility -- If local is set, we know the call is from a publisher. -- set it to be @@REMSERVER -- Otherwise, set it to be local server name -- Note: @@REMSERVER is NULL for local sp calls -- IF LOWER(@local) = 'local' AND @@REMSERVER IS NOT NULL SELECT @publisher = @@REMSERVER ELSE SELECT @publisher = publishingservername() END -- -- Set attribute indicating whether user is 'db_owner'. -- if LOWER(@local) <> 'local' or @local is NULL begin -- Determine whether user has dbowner access if not ((is_srvrolemember('sysadmin') = 1) or (is_member('db_owner') = 1 and sys.fn_MSrepl_ispublished(db_name()) = 1) ) begin select @has_dbowner_access = 0 -- Setting @loc_account to '%' prevents reading the registry for the -- account information at a remote distributor if user isn't authorized. select @loc_account = '%' end end -- -- Get the distribution server -- SELECT @dist_rpcname = srvname, @loc_distributor = datasource, @srvid = srvid, @loc_rpcsrvname = srvname FROM master.dbo.sysservers WHERE srvstatus & 8 <> 0 if @loc_distributor is null GOTO DONE select @rpcsrvlogin = null -- sysoledbusers is for outgoing rpc servers only so it should be -- appropriate for querying the remote login of the distributor link. But -- as a safety measure, we will query sysremotelogins (for incoming RPC -- calls) if no remote login is returned from sysoledbusers to maintain -- full compatibility with the sysxlogins query that we used before. select @rpcsrvlogin = rmtloginame from master.dbo.sysoledbusers where rmtsrvid = @srvid and loginsid is NULL if @rpcsrvlogin is null begin select @rpcsrvlogin = remoteusername from master.dbo.sysremotelogins where remoteserverid = @srvid and sid is NULL end -- -- If remote distribuiton, execute sys.sp_helpdistributor on distribution -- server. -- IF UPPER(@loc_distributor) <> UPPER(@@SERVERNAME) BEGIN SELECT @proc = @dist_rpcname + '.master.sys.sp_helpdistributor' -- -- from publisher -- EXECUTE @retcode = @proc @loc_distributor OUTPUT, @loc_distribdb OUTPUT, @loc_directory OUTPUT, @loc_account OUTPUT, @loc_mindistretention OUTPUT, @loc_maxdistretention OUTPUT, @loc_historyretention OUTPUT, @loc_historycleanupagent OUTPUT, @loc_distribcleanupagent OUTPUT, @@SERVERNAME, @local = 'local', @publisher_type = @loc_publishertype OUTPUT IF @retcode <> 0 or @@ERROR <> 0 RETURN (1) GOTO DONE END -- -- validate the calling publisher -- SELECT @loc_distribdb = distribution_db, @loc_directory = working_directory, @loc_publishertype = publisher_type FROM msdb.dbo.MSdistpublishers WHERE UPPER(name collate database_default ) = UPPER(@publisher) collate database_default IF @@ERROR <> 0 RETURN 1 -- -- If distribution db is NULL, there is no matching distributor. -- This typically would happen when calling on a distributor that -- only has HREPL publishers. This case should result in no output -- IF (@loc_distribdb IS NULL) BEGIN RETURN (0) END -- Security. Connection to remote distributor must have 'sysadmin' or 'db_owner' -- in distribution database authorization. This prevents user from bypassing -- security checks by explicitly setting 'local' parameter in the call. if LOWER(@local) = 'local' begin exec @retcode = sys.sp_MSrepl_isdbowner @loc_distribdb if @retcode <> 1 or @@error <> 0 RETURN (1) end SELECT @loc_mindistretention = min_distretention, @loc_maxdistretention = max_distretention, @loc_historyretention = history_retention FROM msdb.dbo.MSdistributiondbs WHERE name = @loc_distribdb collate database_default -- -- Fetch the distribution account name. -- IF ((@distributor = '%' AND @distribdb = '%' AND @directory = '%' AND @account = '%' AND @min_distretention = -1 AND @max_distretention = -1 AND @history_retention = -1 AND @history_cleanupagent = '%' AND @distrib_cleanupagent = '%' AND @publisher_type = '%' AND @rpcsrvname = '%' ) OR @account IS NULL) and ( platform() & @platform_nt = @platform_nt ) and ( @has_dbowner_access = 1 ) BEGIN declare @instance sysname declare @regkey nvarchar(260) -- not changing for instapi work. hardcoding this path select @instance = convert(sysname, SERVERPROPERTY('InstanceName')) select @regkey = 'SYSTEM\CurrentControlSet\Services\' -- default installation if @instance is null SELECT @regkey = @regkey + 'SQLServerAgent' else SELECT @regkey = @regkey + 'SQLAgent$' + @instance SELECT @proc = 'master.dbo.xp_regread' EXECUTE @retcode = @proc 'HKEY_LOCAL_MACHINE', @regkey, 'ObjectName', @param = @loc_account OUTPUT IF @@ERROR <> 0 OR @retcode <> 0 SELECT @loc_account = NULL END -- -- Fetch the history cleanup agentname. -- IF @loc_distribdb IS NOT NULL SELECT @loc_historycleanupagent = formatmessage (20567, @loc_distribdb) -- -- Fetch the distribution cleanup agent name. -- IF @loc_distribdb IS NOT NULL SELECT @loc_distribcleanupagent = formatmessage (20568, @loc_distribdb) DONE: -- -- If user does not have 'db_owner' authorization, NULL restricted return parameters. -- if @has_dbowner_access = 0 begin select @loc_account = NULL select @loc_mindistretention = NULL select @loc_historyretention = NULL select @loc_historycleanupagent = NULL select @loc_distribcleanupagent = NULL select @rpcsrvlogin = NULL end -- -- If @directory is to be returned and user does not have 'db_owner' access, check for PAL access. -- IF ((@distributor = '%' AND @distribdb = '%' AND @directory = '%' AND @account = '%' AND @min_distretention = -1 AND @max_distretention = -1 AND @history_retention = -1 AND @history_cleanupagent = '%' AND @distrib_cleanupagent = '%' AND @rpcsrvname = '%' and @publisher_type = '%') OR ( @directory is NULL )) AND ( LOWER(@local) <> 'local' or @local is NULL ) AND ( @has_dbowner_access = 0 ) AND (@loc_rpcsrvname is not null and @loc_distribdb is not null) begin -- Check to determine whether the current user is in the PAL -- of any publication that makes use of this publisher. create table #pub (publisher_db sysname, publication sysname) SELECT @proc = RTRIM(@loc_rpcsrvname) + '.' + RTRIM(@loc_distribdb) + '.sys.sp_MSpublication_access' INSERT into #pub (publisher_db, publication) EXEC @retcode = @proc @publisher = @publisher, @operation = N'get_publications', @login = @login if not exists (select * from #pub) select @loc_directory = NULL end -- -- Return result set if no output parameters -- IF (@distributor = '%' AND @distribdb = '%' AND @directory = '%' AND @account = '%' AND @min_distretention = -1 AND @max_distretention = -1 AND @history_retention = -1 AND @history_cleanupagent = '%' AND @distrib_cleanupagent = '%' AND @rpcsrvname = '%' and @publisher_type = '%') SELECT 'distributor' = @loc_distributor, 'distribution database' = @loc_distribdb, 'directory' = @loc_directory, 'account' = @loc_account, 'min distrib retention' = @loc_mindistretention, 'max distrib retention' = @loc_maxdistretention, 'history retention' = @loc_historyretention, 'history cleanup agent' = @loc_historycleanupagent, 'distribution cleanup agent' = @loc_distribcleanupagent, 'rpc server name' = @loc_rpcsrvname, 'rpc login name' = @rpcsrvlogin, 'publisher type' = @loc_publishertype -- -- Return output parameters if requested. -- IF @distributor IS NULL SELECT @distributor = @loc_distributor IF @distribdb IS NULL SELECT @distribdb = @loc_distribdb IF @directory IS NULL SELECT @directory = @loc_directory IF @account IS NULL SELECT @account = @loc_account IF @min_distretention IS NULL SELECT @min_distretention = @loc_mindistretention IF @max_distretention IS NULL SELECT @max_distretention = @loc_maxdistretention IF @history_retention IS NULL SELECT @history_retention = @loc_historyretention IF @history_cleanupagent IS NULL SELECT @history_cleanupagent = @loc_historycleanupagent IF @distrib_cleanupagent IS NULL SELECT @distrib_cleanupagent = @loc_distribcleanupagent IF @publisher_type IS NULL SELECT @publisher_type = @loc_publishertype IF @rpcsrvname IS NULL BEGIN -- -- BUGBUG : The value for @rpcsrvname must match the value returned by -- sp_MSrepl_getdistributorinfo or we will see indefinite blocking -- in some areas of our code. Example-incremental add article. So -- if you make a change here make it in sp_MSrepl_getdistributorinfo -- -- For the following cases use LOCAL SERVER NAME -- 1. Hetero will always use local server -- 2. Local distributor with sysadmin access (used to avoid blocking issues) IF @loc_publishertype != N'MSSQLSERVER' OR (IS_SRVROLEMEMBER('sysadmin') = 1 AND UPPER(@loc_distributor) = UPPER(@@SERVERNAME)) BEGIN SELECT @rpcsrvname = srvname FROM master.dbo.sysservers WHERE UPPER(srvname collate database_default ) = UPPER(@loc_distributor) END -- Remote distributor or local with non-sysadmin rights ELSE BEGIN SELECT @rpcsrvname = @loc_rpcsrvname END END RETURN (0) END
No comments:
Post a Comment