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_MSenumallpublications(nvarchar @publisherdb, tinyint @replication_type
, nvarchar @agent_login
, bit @security_check
, nvarchar @vendor_name
, nvarchar @publication
, bit @hrepl_pub
, bit @empty_tranpub)
MetaData:
create procedure sys.sp_MSenumallpublications ( @publisherdb sysname = N'%', @replication_type tinyint = null, -- by default return all types of publication @agent_login sysname = NULL, @security_check bit = 1, -- Security check is ignored, although parameter has not been removed @vendor_name sysname = NULL, -- Use vender name to filter the result of third party publications. @publication sysname = NULL, @hrepl_pub bit = 0, -- Return HREPL publications as well @empty_tranpub bit = 0 -- Return tran publication with no articles ) as BEGIN set nocount on declare @dbname sysname ,@trans tinyint ,@merge tinyint ,@tranmerge tinyint ,@3rdparty tinyint ,@retcode int ,@category int ,@loc_server sysname ,@publisher sysname ,@distributor sysname ,@distribdb sysname ,@login sysname ,@proc nvarchar(255) ,@distbit int ,@is_user_admin bit ,@same_as_user bit -- @security_check parameter is ignored. Security is always checked. -- UI: If the distributor is not installed, return empty result if not exists ( SELECT * FROM master.dbo.sysservers WHERE srvstatus & 8 <> 0 ) begin return (0) end -- -- Initializations -- select @3rdparty = 0 ,@trans = 1 ,@merge = 2 ,@tranmerge = 3 ,@login = suser_sname(suser_sid()) ,@distbit = 16 ,@is_user_admin = case when (is_srvrolemember('sysadmin') = 1) then 1 else 0 end ,@loc_server = CONVERT(sysname, ServerProperty('ServerName')) -- -- If the agent login is not specified - use current login -- UI: Win95 subscriber will send in null agent_login -- if @agent_login is null begin select @agent_login = @login ,@same_as_user = 1 end else begin select @same_as_user = case when (suser_sid(@agent_login, 0) = suser_sid()) then 1 else 0 end end if (@publisherdb = N'%') begin -- Temp table to enumerate the publisher dbs create table #pubdbs ( publisher_db sysname not null, category int ) end -- Temp table to enumerate the publications create table #MSenumpublications ( publisher_db sysname collate database_default not null, publication sysname collate database_default not null, replication_type tinyint NOT NULL, immediate_sync bit NOT NULL, allow_pull bit NOT NULL, allow_anonymous bit NOT NULL, enabled_for_internet bit NOT NULL, repl_freq tinyint NOT NULL, immediate_sync_ready bit NOT NULL, allow_sync_tran bit NOT NULL, independent_agent bit NOT NULL, is_db_owner int NOT NULL, thirdparty_flag bit NOT NULL, vendor_name sysname collate database_default null, publisher sysname collate database_default null, publisher_type sysname collate database_default null, description nvarchar(255) collate database_default null, distribution_db sysname collate database_default null, allow_queued_tran bit not null, allow_dts bit not null, thirdparty_options int null, queue_type int null, dynamic_filters bit not null default 0 ) if (@is_user_admin = 0) or (@same_as_user = 0) begin -- Temp table to enumerate the distribution dbs create table #distdbs ( publisher sysname collate database_default not null, distributor sysname collate database_default not null, distribdb sysname collate database_default not null ) if (@is_user_admin = 0) begin -- Temp table to list publications that current login has access to DECLARE @curuser_publications TABLE ( publisher_db sysname collate database_default not null, publication sysname collate database_default not null ) end if (@same_as_user = 0) begin -- Temp table to list publications that agent login has access to DECLARE @agent_publications TABLE ( publisher_db sysname collate database_default not null, publication sysname collate database_default not null ) end end -- -- Return everything if @replication_type is not in (@3rdparty, @trans, @merge) -- if @replication_type not in (@3rdparty, @trans, @merge, @tranmerge) begin select @replication_type = null end -- -- Get published databases -- if (@publisherdb = N'%') begin insert into #pubdbs select name, category from master.dbo.sysdatabases where has_dbaccess(name) = 1 and ( -- process based on replication type (@replication_type = @tranmerge and (category & 1 = 1 or category & 4 = 4 or (@hrepl_pub = 1 and category & @distbit = @distbit))) OR (@replication_type = @trans and (category & 1 = 1 or (@hrepl_pub = 1 and category & @distbit = @distbit))) OR (@replication_type = @merge and category & 4 = 4) OR (@replication_type = @3rdparty and category & @distbit = @distbit) OR (@replication_type is null and (category & 1 = 1 or category & 4 = 4 or category & @distbit = @distbit)) ) end else begin select @dbname = name ,@category = category from master.dbo.sysdatabases where name = @publisherdb and has_dbaccess(name) = 1 and ( -- process based on replication type (@replication_type = @tranmerge and (category & 1 = 1 or category & 4 = 4 or (@hrepl_pub = 1 and category & @distbit = @distbit))) OR (@replication_type = @trans and (category & 1 = 1 or (@hrepl_pub = 1 and category & @distbit = @distbit))) OR (@replication_type = @merge and category & 4 = 4) OR (@replication_type = @3rdparty and category & @distbit = @distbit) OR (@replication_type is null and (category & 1 = 1 or category & 4 = 4 or category & @distbit = @distbit)) ) end -- -- Get the publication information now -- if (@publisherdb = N'%') begin -- -- wild card - all publications -- declare #curPubDb CURSOR LOCAL FAST_FORWARD FOR select publisher_db, category from #pubdbs FOR READ ONLY open #curPubDb fetch #curPubDb into @dbname, @category while (@@fetch_status >= 0) begin -- enumerate the publications based on replication type and category if (@replication_type is null or @replication_type in (@tranmerge,@trans)) and (@category & 1 = 1 or (@hrepl_pub = 1 and @category & @distbit = @distbit)) begin select @proc = quotename(@dbname) + N'.sys.sp_MSenumtranpublications' exec @retcode = @proc @publication, @category, @empty_tranpub if @@ERROR <> 0 or @retcode <> 0 return (1) end if (@replication_type is null or @replication_type in (@tranmerge,@merge)) and (@category & 4 = 4) begin select @proc = quotename(@dbname) + N'.sys.sp_MSenummergepublications' exec @retcode = @proc @publication, @category if @@ERROR <> 0 or @retcode <> 0 return (1) end if (@replication_type is null or @replication_type = @3rdparty) and (@category & @distbit = @distbit) begin select @proc = quotename(@dbname) + N'.sys.sp_MSenum3rdpartypublications' exec @retcode = @proc @vendor_name, @publication if @@ERROR <> 0 or @retcode <> 0 return (1) end -- get next publisher db fetch #curPubDb into @dbname, @category end CLOSE #curPubDb DEALLOCATE #curPubDb end else begin -- specific publication -- enumerate the publications based on replication type and category -- if (@replication_type is null or @replication_type in (@tranmerge,@trans)) and (@category & 1 = 1 or (@hrepl_pub = 1 and @category & @distbit = @distbit)) begin select @proc = quotename(@dbname) + N'.sys.sp_MSenumtranpublications' exec @retcode = @proc @publication, @category, @empty_tranpub if @@ERROR <> 0 or @retcode <> 0 return (1) end if (@replication_type is null or @replication_type in (@tranmerge,@merge)) and (@category & 4 = 4) begin select @proc = quotename(@dbname) + N'.sys.sp_MSenummergepublications' exec @retcode = @proc @publication, @category if @@ERROR <> 0 or @retcode <> 0 return (1) end if (@replication_type is null or @replication_type = @3rdparty) and (@category & @distbit = @distbit) begin select @proc = quotename(@dbname) + N'.sys.sp_MSenum3rdpartypublications' exec @retcode = @proc @vendor_name, @publication if @@ERROR <> 0 or @retcode <> 0 return (1) end end -- -- Get distributor info for local SQL server publisher (if any) -- EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT, @distribdb = @distribdb OUTPUT IF @@error <> 0 OR @retcode <> 0 RETURN (1) -- -- Verify that a distributor db exists (only if not returning HREPL as well) -- IF @hrepl_pub = 0 AND @distribdb IS NULL BEGIN RAISERROR (14071, 16, -1) RETURN (1) END IF @distribdb IS NOT NULL AND @distributor IS NOT NULL BEGIN if (@is_user_admin = 0) or (@same_as_user = 0) begin INSERT INTO #distdbs VALUES (@loc_server, @distributor, @distribdb) end -- Set local publisher distrib db UPDATE #MSenumpublications SET distribution_db = @distribdb WHERE publisher = @loc_server collate database_default END -- -- Include local heterogeneous publications -- IF @hrepl_pub = 1 AND @distributor = CONVERT(sysname, ServerProperty('ServerName')) BEGIN if (@is_user_admin = 0) or (@same_as_user = 0) begin -- Get local publisher/distribution db pairs with HREPL pubs INSERT INTO #distdbs SELECT name, @loc_server, distribution_db FROM msdb.dbo.MSdistpublishers WHERE publisher_type != N'MSSQLSERVER' AND sys.fn_MSrepl_istranpublished(distribution_db, 1) = 1 end END -- -- we will skip PAL check if sysadmin and agent login is not different -- if (@is_user_admin = 0) or (@same_as_user = 0) begin -- Check publication access for each distributor db declare #curDistDb CURSOR LOCAL FAST_FORWARD FOR select publisher, distributor, distribdb from #distdbs FOR READ ONLY open #curDistDb fetch #curDistDb into @publisher, @distributor, @distribdb while (@@fetch_status >= 0) BEGIN -- sp_MSpublication_access returns those publications that the passed login has access to. -- If the value passed in for @agent_login is not the same as the current user, a separate -- table is populated with those publications that the agent has access to. This table is -- used to set the agent_access field returned. -- -- Information is returned on those publications that the current user has access to. -- SELECT @proc = case when (@distributor IS NOT NULL) THEN quotename(@distributor) + N'.' else N' ' end + quotename(@distribdb) + N'.sys.sp_MSpublication_access' -- -- Get accessible publication list if user is not sysadmin -- if (@is_user_admin = 0) begin insert into @curuser_publications EXEC @retcode = @proc @publisher = @publisher, @operation = N'get_publications', @login = @login end -- -- If agent login is different -- Get accessible publication list for agent login -- if (@same_as_user = 0) begin insert into @agent_publications EXEC @retcode = @proc @publisher = @publisher, @operation = N'get_publications', @login = @agent_login end -- -- commit open tran in case -- insert into exec failed. -- while(@@trancount <> 0) commit tran -- Get next dist db fetch #curDistDb into @publisher, @distributor, @distribdb END CLOSE #curDistDb DEALLOCATE #curDistDb end -- if (@is_user_admin = 0 or @same_as_user = 0) -- -- Return resultset -- select pub.publisher_db, pub.publication, pub.replication_type, pub.immediate_sync, pub.allow_pull, pub.allow_anonymous, pub.enabled_for_internet, pub.repl_freq, pub.immediate_sync_ready, pub.allow_sync_tran, pub.independent_agent, case when (@same_as_user = 1 or exists (select * from @agent_publications as agent where agent.publisher_db = pub.publisher_db and agent.publication = pub.publication) ) then convert(bit,1) else convert(bit,0) end as [agent_access], pub.thirdparty_flag, pub.vendor_name, pub.publisher, pub.description, pub.distribution_db, allow_queued_tran, allow_dts, pub.thirdparty_options, pub.queue_type, pub.dynamic_filters, pub.publisher_type from #MSenumpublications as pub -- Choose publications if sysadmin or pub dbowner or -- acessible by PAL where @is_user_admin = 1 or pub.is_db_owner = 1 or exists (select * from @curuser_publications as access where pub.publisher_db = access.publisher_db and pub.publication = access.publication) order by pub.publication, pub.publisher_db -- -- all done -- return (0) END
No comments:
Post a Comment