May 25, 2012

sp_MSreplcheck_pull (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_MSreplcheck_pull(nvarchar @publication
, bit @raise_fatal_error
, uniqueidentifier @pubid
, nvarchar @given_login
, nvarchar @publisher)

MetaData:

 create procedure sys.sp_MSreplcheck_pull  
(
@publication sysname= NULL,
@raise_fatal_error bit = 1,
@pubid uniqueidentifier = NULL,
@given_login sysname = NULL,
@publisher sysname = NULL
)
as
begin
declare @login sysname
,@dbname sysname
,@retcode int
,@err_level int
,@tranpubid int
,@publisher_type sysname

--
-- Special processing if explicit login was not provided
--
if @given_login is null
begin
--
-- explict login not provided
-- sysadmin or db_owner have access
--
if (is_srvrolemember('sysadmin') = 1 or is_member('db_owner') = 1)
return (0)
end
--
-- Get the current login
-- NOTE: why is user_name() not being used
--
select @login = suser_sname(suser_sid())
--
-- Special processing if explicit login was provided
--
if (@given_login is not null)
begin
--
-- More special processing
--
if (@login = @given_login)
begin
--
-- given login is same as current login
-- sysadmin or db_owner have access
--
if (is_srvrolemember('sysadmin') = 1 or is_member('db_owner') = 1)
return (0)
end
else
begin
--
-- To determine whether another login has access to a publication
-- requires current user to be db_owner in the publishing database
--
if is_member('db_owner') <> 1
begin
raiserror (21050, 16, -1)
return (1)
end
--
-- given login is not same as current login
-- if given login is sysadmin or db_owner member then it has access
--
if (is_srvrolemember('sysadmin', @given_login) = 1 or sys.fn_isrolemember(1, @given_login, NULL) = 1)
return (0)
select @login = @given_login
end
end
--
-- initialize
--
if (@publisher is null)
select @publisher = publishingservername()
if (@publication is not null)
begin
select @pubid = NULL
if object_id('dbo.syspublications') is not null
begin
--
-- get the publisher type
--
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @publisher,
@publisher_type = @publisher_type OUTPUT
IF @@error <> 0
BEGIN
RAISERROR (14071, 16, -1)
return (1)
END
--
-- get the pubid
--
SELECT @tranpubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)
end
if object_id('dbo.sysmergepublications') is not null
select @pubid = pubid from sysmergepublications where name = @publication and upper(publisher) = upper(@publisher)
if (@tranpubid is null and @pubid is null)
begin
raiserror(20026, 16, -1, @publication)
return 1
end
end
else if (@pubid is not null)
begin
select @publication = NULL
if object_id('dbo.sysmergepublications') is not null
select @publication = name from sysmergepublications where pubid = @pubid and upper(publisher) = upper(@publisher)
if (@publication is null)
begin
raiserror(21723, 16, -1, 'sp_MSreplcheck_pull')
return 1
end
end
--
-- check the PAL roles
--
if (@tranpubid is null and @pubid is null)
begin
declare @lrpc nvarchar(400)
,@hasaccess int
--
-- generic check
-- user must be member of any existing PAL roles
-- check the current database first
--
if (sys.fn_isrolemember(0, @login, NULL) = 1)
begin
--
-- has access
--
return 0
end
else
begin
--
-- check the other publishing dbs
--
declare #hcpubdb cursor local fast_forward for
select name
from master.dbo.sysdatabases
where has_dbaccess(name) = 1
and ((category & 1 != 0) or (category & 4 != 0))
and name != db_name()
and name not in
(
N'master' COLLATE DATABASE_DEFAULT,
N'tempdb' COLLATE DATABASE_DEFAULT,
N'msdb' COLLATE DATABASE_DEFAULT,
N'model' COLLATE DATABASE_DEFAULT
)
and sys.fn_MSrepl_isdistdb (name) = 0
for read only

open #hcpubdb
fetch #hcpubdb into @dbname
while (@@fetch_status <> -1)
begin
--
-- Check if the user is PAL member in this db
--
select @hasaccess = 0
,@lrpc = quotename(@dbname) + '.sys.sp_MScheck_palroleinpubdb'
exec @retcode = @lrpc @login, @hasaccess output
if (@retcode != 0 or @@error != 0)
begin
return 1
end
if (@hasaccess = 1)
begin
--
-- has access
--
return 0
end
--
-- get next publishing db
--
fetch #hcpubdb into @dbname
end
close #hcpubdb
deallocate #hcpubdb
--
-- if we have reached here
-- it mean the user has is not PAL member of any publication in this server
--
goto error
end
end
else if (@pubid is not null)
begin
if @given_login is not NULL
begin
-- check if the given login is a member of the specific merge publication's pal role
declare @role sysname
declare @pubidstr nvarchar(40)

exec sys.sp_MSguidtostr @pubid, @pubidstr output
set @role = 'MSmerge_' + @pubidstr

if exists (select * from sys.database_role_members
where role_principal_id in (select principal_id
from sys.database_principals
where name = @role)
and member_principal_id = (select dp.principal_id
from sys.database_principals as dp join master.dbo.syslogins as s
on dp.sid = s.sid
and s.name = @given_login))
-- has access
return 0
end
else if (1 = {fn ISPALUSER(@pubid)})
begin
--
-- has access
--
return 0
end
end
else
begin
--
-- check for specific transactional publication
-- the user must be member of specific PAL role
--
if (sys.fn_isrolemember(2, @login, @tranpubid) = 1)
begin
--
-- has access
--
return 0
end
end
--
-- if we reach here, we don't have access. Return error
--
error:
select @err_level = case when (@raise_fatal_error = 1) then 14 else 10 end
IF @publication IS NOT NULL
RAISERROR (21049, @err_level, -1, @login, @publication)
ELSE
RAISERROR (21688, @err_level, -1, @login, @publisher)
return (1)
end

No comments:

Post a Comment

Total Pageviews