April 27, 2012

sp_helpdistpublisher (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_helpdistpublisher(nvarchar @publisher
, bit @check_user)

MetaData:

 create procedure sys.sp_helpdistpublisher  
(
@publisher sysname = N'%',
@check_user bit = 0 -- filter entries for distribution dbs where user is a owner
)
AS
BEGIN
SET NOCOUNT ON

declare @username sysname
,@command nvarchar(4000)
,@fallowaccess bit
,@db_name sysname
,@cmdproc nvarchar(1000)
,@has_pm bit
,@distbit int
,@retcode int
,@publisher_name sysname
-- this will be used for user check
declare @distdbs table (publisher sysname, name sysname, primary key(publisher,name))
declare @MSdistlogins table (
name sysname
,distribution_db sysname
,login sysname
,password nvarchar(524)
,primary key(name, distribution_db, login))

--
-- create temp table for collection of distribution dbs
-- this will be used for user check
--
--
if object_id('tempdb..#distdbs') is not NULL
begin
drop table #distdbs
end

create table #distdbs (publisher sysname collate database_default, name sysname collate database_default)

if object_id('tempdb..#MSdistlogins') is not NULL
begin
drop table #MSdistlogins
end

create table #MSdistlogins (name sysname, distribution_db sysname, login sysname, password nvarchar(524))
--

--
-- @publisher : validate
--
IF @publisher IS null
begin
raiserror (14043, 16, -1, '@publisher', 'sp_helpdistpublisher')
return (1)
end
--
-- Check to make sure this is a distributor
--
IF NOT EXISTS (select * from master.sys.servers
where UPPER(data_source collate database_default) = UPPER(@@SERVERNAME)
AND is_distributor = 1)
begin
if @publisher <> N'%'
begin
raiserror (14114, 16, -1, @@SERVERNAME)
return(1)
end
else
return(0)
end

IF @publisher <> N'%' AND NOT EXISTS (select * from msdb.dbo.MSdistpublishers
where name collate database_default = UPPER(@publisher))
begin
raiserror (14080, 11, -1, @publisher)
return (1)
end
--
-- allow access to login information if running as sysadmin
-- If sysadmin, no need for filtering - override the input for user check
--
if (is_srvrolemember(N'sysadmin') = 1)
select @fallowaccess = 1 ,@check_user = 0
else
select @fallowaccess = 0, @check_user = 1 -- Force nonsysadmin user to be always checked
--
-- Processing for non sysadmin user
--
if (@fallowaccess = 0 and @check_user = 1)
begin
--
-- Has publisher been specified
--
if (@publisher <> N'%')
begin
--
-- doing specific publisher query.
-- force a dbowner check in the specific distribution db
--
select @db_name = distribution_db
from msdb.dbo.MSdistpublishers
where name collate database_default = UPPER(@publisher)

select @cmdproc = quotename(@db_name) + N'.sys.sp_executesql'
, @has_pm = 0
exec @cmdproc N'if is_member(N''db_owner'') = 1 set @has_pm = 1',
N'@has_pm bit output',
@has_pm output
if @@ERROR <> 0
return(1)
--
-- if we are the DBO of the specific distribution db AND we are issuing the
-- query from a replication agent - then allow access to login info for specific
-- publisher case - Queue reader agent does this
--
if (@has_pm = 1 and sessionproperty(N'replication_agent') = 1)
select @fallowaccess = 1

-- if no access yet, see if the user is 'repl_monitor',
-- or in the PAL for any publications associated with this publisher
if @has_pm = 0
begin
select @cmdproc = quotename(@db_name) + N'.sys.sp_MSrepl_DistDBPALAccess'
exec @retcode = @cmdproc
@publisher = @publisher

if @@ERROR <> 0
return(1)

if @retcode = 0
BEGIN
select @has_pm = 1
END
ELSE
BEGIN
-- Security: avoid info disclosure for users that don't have access.
-- Return publisher doesn't exist instead of empty set
raiserror (14080, 11, -1, @publisher)
return (1)
END
end
--
-- If use has permissions then store this db_name
--
if ((@has_pm = 1) or (sessionproperty(N'replication_agent') = 1))
insert into @distdbs (publisher, name) values (upper(@publisher), @db_name)
end
else
begin
--
-- publisher not specified and user check enabled
-- perform db owner check for distribution dbs
--
declare #hCdatabase CURSOR LOCAL FAST_FORWARD FOR
select msdp.name,
msdp.distribution_db
from msdb.dbo.MSdistpublishers msdp
join master.sys.databases sysdb
on msdp.distribution_db collate database_default = sysdb.name collate database_default
and sysdb.is_distributor = 1
and has_dbaccess(sysdb.name) = 1
for read only

open #hCdatabase
fetch next from #hCdatabase into @publisher_name, @db_name
while (@@fetch_status <> -1)
begin
-- Allow access if the user is 'db_owner', 'replmonitor' or in the PAL for any
-- publications associated with any publisher using this distribution database
begin
select @cmdproc = quotename(@db_name) + N'.sys.sp_MSrepl_DistDBPALAccess'
exec @retcode = @cmdproc @publisher = @publisher_name
if @@ERROR <> 0
return(1)

if @retcode = 0
insert into @distdbs (publisher, name) values (upper(@publisher_name), @db_name)
end
-- fetch next entry
fetch next from #hCdatabase into @publisher_name, @db_name
end
close #hCdatabase
deallocate #hCdatabase
end -- publisher not specified
end -- if (@fallowaccess = 0 and @check_user = 1) block

-- load the logins table
insert into @MSdistlogins (name, distribution_db, login, password)
SELECT name, distribution_db, login, sys.fn_repldecryptver4(password)
FROM msdb.dbo.MSdistpublishers

--
declare #curDistDB CURSOR LOCAL FAST_FORWARD FOR
select name,
distribution_db
from msdb.dbo.MSdistpublishers

open #curDistDB

fetch next from #curDistDB into @publisher_name, @db_name
while (@@fetch_status <> -1)
begin
select @command = QUOTENAME(@db_name) + N'.sys.sp_MShelpdistpublisher'

insert into @MSdistlogins
exec @command @publisher = @publisher_name, @db_name
if @@ERROR <> 0 return(1)
-- fetch next entry
fetch next from #curDistDB into @publisher_name, @db_name
end
close #curDistDB
deallocate #curDistDB
--

--
-- Generate result query
--
SELECT p.name,
p.distribution_db,
p.security_mode,
-- Not to return login unless allowed access.
N'login' = CASE
WHEN (@fallowaccess = 1) THEN p.login
ELSE cast(NULL as sysname)
END,
-- Not to return password unless allowed access.
N'password' = CASE
WHEN (@fallowaccess = 1) THEN msdl.password
ELSE cast(NULL as sysname)
END,
p.active,
p.working_directory,
p.trusted,
p.thirdparty_flag,
p.publisher_type,
N'publisher_data_source' = s.data_source
FROM msdb.dbo.MSdistpublishers as p
join master.sys.servers as s
on p.name collate database_default = s.name collate database_default
join @MSdistlogins as msdl
on msdl.name = p.name collate database_default
AND msdl.distribution_db = p.distribution_db collate database_default
AND msdl.login = p.login collate database_default
WHERE ((@publisher = N'%') OR (p.name collate database_default= UPPER(@publisher)))
AND (@check_user = 0
OR EXISTS (SELECT *
FROM @distdbs as d
WHERE d.name = p.distribution_db collate database_default
AND d.publisher = p.name collate database_default))
--
-- all done
--
return 0
end

No comments:

Post a Comment

Total Pageviews