Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_helpdistributor

  No additional text.


Syntax


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

 
Last revision 2008RTM
See also

  sp_adddistributor (Procedure)
sp_addmergepushsubscription_agent (Procedure)
sp_addmergesubscription (Procedure)
sp_changemergepublication (Procedure)
sp_changemergesubscription (Procedure)
sp_deletetracertokenhistory (Procedure)
sp_dropmergepublication (Procedure)
sp_dropmergesubscription (Procedure)
sp_enumcustomresolvers (Procedure)
sp_helpdistributor_properties (Procedure)
sp_helpmergesubscription (Procedure)
sp_helptracertokenhistory (Procedure)
sp_helptracertokens (Procedure)
sp_lookupcustomresolver (Procedure)
sp_mergesubscriptionsummary (Procedure)
sp_MSaddmergepub_snapshot (Procedure)
sp_MSclear_dynamic_snapshot_location (Procedure)
sp_MScopyscriptfile (Procedure)
sp_MScopyscriptfile_merge (Procedure)
sp_MScreatemergedynamicsnapshot (Procedure)
sp_MSdropmergedynamicsnapshotjob (Procedure)
sp_MSdropmergepub_snapshot (Procedure)
sp_MSdrop_replcom (Procedure)
sp_MSgetisvalidwindowsloginfromdistributor (Procedure)
sp_MSgetpartitionsnapshotfolder (Procedure)
sp_MSget_max_used_identity_from_distributor (Procedure)
sp_MShelpmergepub_withoutrownumbers (Procedure)
sp_MSmergepublishdb (Procedure)
sp_MSmergepushsubscriptionagentjobcontrol (Procedure)
sp_MSmerge_log_idrange_alloc_on_distributor (Procedure)
sp_MSpeersendtopologyinfo (Procedure)
sp_MSremovedbreplication (Procedure)
sp_MSrepl_addlogreader_agent (Procedure)
sp_MSrepl_addpushsubscription_agent (Procedure)
sp_MSrepl_changelogreader_agent (Procedure)
sp_MSrepl_changepublication_snapshot (Procedure)
sp_MSrepl_changesubscription (Procedure)
sp_MSrepl_getdistributorinfo (Procedure)
sp_MSrepl_helplogreader_agent (Procedure)
sp_MSrepl_helppublication_snapshot (Procedure)
sp_MSrepl_subscriptionagentjobcontrol (Procedure)
sp_MSrepl_subscriptionsummary (Procedure)
sp_MSsetupnosyncsubscriptionwithlsn (Procedure)
sp_reinitmergesubscription (Procedure)
sp_vupgrade_registry_custom_resolver_katmai (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash