April 27, 2012

sp_helpreplicationdboption (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
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_helpreplicationdboption(nvarchar @dbname
, nvarchar @type
, bit @reserved)

MetaData:

   
--
-- Name:
-- sp_helpreplicationdboption
--
-- Description:
-- This stored procedure shows databases that have the replication
-- option enabled. It is executed at the publisher on any database.
-- It is used in snapshot, transactional, and merge replication.
-- It is not valid for heterogeneous publishers.
--
-- When the @dbname is speciified, a result set is returned if the
-- database meets the criteria specified in @type.
--
-- When the @dbname parameter is NULL, the result set includes entries
-- for all databases at the publisher that meet the criteria specified
-- in @type.
--
-- The user must be a member of the db_owner role of a publishing database
-- or in the PAL for a publication associated with the publishing database
-- to retrieve information for that database.
--
-- Security:
-- 'sysadmin', db_owner of publishing database, PAL for publication
-- associated with publishing databaseDB
-- Requires Certificate signature for catalog access
--
-- Returns:
-- Result set of database name, database id, transactional publisher,
-- merge publisher, current user is dbowner, database is readonly.
--
-- Owner:
-- <current owner>
--
create procedure sys.sp_helpreplicationdboption
(
@dbname sysname = N'%'
,@type sysname = N'replication allowed'
,@reserved bit = 0 -- 1 = get publication and pull subscription info
)
AS
BEGIN
SET NOCOUNT ON

--
-- Declarations.
--

DECLARE @retcode int
,@typebit int
,@distbit int -- bit to distinguish distribution databases
,@dbowner bit
,@issysadmin bit
,@replication_db sysname
,@db_category int
,@inPAL int
,@inPALrole bit
,@transpublish bit
,@mergepublish bit
,@cmd nvarchar(500)

SELECT @distbit = 16
-- optimize security check for sysadmin and dbo
,@issysadmin = case when (IS_SRVROLEMEMBER(N'sysadmin') = 1) then 1 else 0 end
,@inPAL = 0
,@inPALrole = 0
,@typebit = case
when (lower(@type) like N'publish%') then 1
when (lower(@type) like N'subscribe%') then 2
when (lower(@type) like N'merge publish%') then 4
when (lower(@type) like N'merge subscribe%') then 8
when (lower(@type) like N'replication allowed%') then 0
else null end

if @typebit is null
begin
raiserror(14091,-1,-1)
return 1
end
--
-- Parameter Check: @dbname.
-- Check to make sure that the database name conforms to the rules
-- for identifiers.
--
IF @dbname <> N'%'
BEGIN
EXECUTE @retcode = sys.sp_validname @dbname
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
END

--
-- create temp table for resultset
--
CREATE TABLE #replicationdbs
(
name sysname primary key,
id int identity NOT NULL,
transpublish bit not null,
mergepublish bit not null,
dbowner bit not null,
dbreadonly bit not null,
haspublications bit NULL,
haspullsubscriptions bit NULL
)
--
-- populate the table
--
INSERT INTO #replicationdbs (name, transpublish, mergepublish, dbowner, dbreadonly)
SELECT name
,is_published
,is_merge_published
,case when (@issysadmin = 1) then 1 else 0 end
,is_read_only
FROM master.sys.databases
WHERE ((@dbname = N'%') OR (name = @dbname))
AND ((@typebit = 0) or (is_published = 1) or (is_merge_published = 1) or (is_subscribed = 1))
AND (HAS_DBACCESS ( name ) = 1)
AND (@typebit != 0 OR is_distributor = 0) -- Filter out distribution databases
AND (@typebit != 0 OR name not in (N'master',N'model',N'tempdb',N'msdb',N'MSSQLWeb')) -- filter our system dbs
AND source_database_id is NULL -- exclude snapshot files

--
-- enumerate database collection
--
DECLARE #hCdboinfo CURSOR LOCAL FAST_FORWARD FOR
SELECT name, transpublish, mergepublish, dbowner
FROM #replicationdbs

OPEN #hCdboinfo
FETCH #hCdboinfo INTO @replication_db, @transpublish, @mergepublish, @dbowner
WHILE (@@fetch_status <> -1)
BEGIN
--
-- initialize
--
select @inPAL = 0
,@inPALrole = 0
--
-- skip dbowner check when sysadmin
--
if (@dbowner = 0)
begin
exec @dbowner = sys.sp_MSrepl_isdbowner @dbname = @replication_db
if (@dbowner = 1)
begin
update #replicationdbs
set dbowner = 1
where name = @replication_db
end
end
--
-- skip PAL check when sysadmin or dbo
--
if (@dbowner = 0)
begin
--
-- Check to see if the user has PAL access to a database publication
--
if (@transpublish = 1)
begin
-- Check PAL for transactional publication
select @cmd = quotename(@replication_db) + N'.sys.sp_MScheck_palroleinpubdb '
EXEC @retcode = @cmd @login = NULL
,@hasaccess = @inPAL output
end

if (@mergepublish = 1)
begin
-- Check PAL role for merge publication
select @cmd = quotename(@replication_db) + N'.sys.sp_MSrepl_IsUserInAnyPAL '
EXEC @retcode = @cmd
@raise_error = 0
if @@error = 0 and @retcode = 0
select @inPALrole = 1
end
end
--
-- Valid entry if user has access
--
if (@dbowner = 0 and @inPAL = 0 and @inPALrole = 0)
begin
DELETE #replicationdbs where name = @replication_db
end
else if (@reserved = 1)
begin
--
-- get publication and pull subscription information
--
select @cmd = quotename(@replication_db) + N'.sys.sp_MSrepl_helpreplicationdboptionex '
EXEC @retcode = @cmd
@name = @replication_db
,@transpublish = @transpublish
,@mergepublish = @mergepublish
end
--
-- get next db entry
--
FETCH #hCdboinfo INTO @replication_db, @transpublish, @mergepublish, @dbowner
END
CLOSE #hCdboinfo
DEALLOCATE #hCdboinfo
--
-- return resultset
--
if (@reserved = 0)
begin
SELECT
name
,id
,transpublish
,mergepublish
,dbowner
,dbreadonly
FROM #replicationdbs order by name
end
else
begin
SELECT
name
,id
,transpublish
,mergepublish
,dbowner
,dbreadonly
,haspublications
,haspullsubscriptions
FROM #replicationdbs order by name
end
-- all done
return 0
END

No comments:

Post a Comment

Total Pageviews