April 25, 2012

sp_grant_publication_access (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_grant_publication_access(nvarchar @publication
, nvarchar @login
, nvarchar @reserved
, nvarchar @publisher)

MetaData:

 create procedure sys.sp_grant_publication_access  
(
@publication sysname,
@login sysname,
@reserved nvarchar(10) = NULL, -- 'init'
@publisher sysname = NULL
)
AS
BEGIN
set nocount on

-- This stored procedure can be called repeatedly.
DECLARE @distribdb sysname,
@distproc nvarchar (300),
@retcode int,
@dist_rpcname sysname,
@database sysname,
@publisher_type sysname,
@loc_publisher sysname,
@role sysname,
@pubid uniqueidentifier,
@pubidtran int,
@pubidstr nvarchar(40),
@user sysname,
@fmergepub bit,
@ftranpub bit,
@skip bit,
@login2 sysname,
@login_id int,
@sid varbinary(85),
@db_name sysname,
@isloginsysadmin int,
@issqlpublisher bit,
@version int,
@versionyukon int,
@initinfo nvarchar(max)

-- Security Check
exec @retcode = sys.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
return(1)

-- Set publisher name if not supplied
select @loc_publisher = case when (@publisher is null) then publishingservername()
else @publisher end

-- Get publisher info
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @loc_publisher,
@rpcsrvname = @dist_rpcname OUTPUT,
@distribdb = @distribdb OUTPUT,
@publisher_type = @publisher_type OUTPUT,
@version = @version OUTPUT
IF @@error <> 0
BEGIN
RAISERROR (14071, 16, -1)
return (1)
END

IF @retcode <> 0 OR @distribdb IS NULL OR @dist_rpcname IS NULL
BEGIN
RAISERROR (14071, 16, -1)
return(1)
END

-- Restrict specification of publisher to HREPL only
select @issqlpublisher = case when (@publisher_type = N'MSSQLSERVER') then 1 else 0 end
IF @issqlpublisher = 1 AND @publisher IS NOT NULL
BEGIN
RAISERROR(21606, 16, -1, '@publisher', 'NULL')
RETURN (1)
END
--
-- Use current context if @login is null
-- NOTE: if the login is provisioned (no explicitly created
-- using CREATE LOGIN since it may be a member of GROUP) then
-- 1)suser_id(@login) will return NULL - the login does not
-- have any explicit entry in master.dbo.syslogins
-- 2)suser_id() will map to login token information
--
if @login is null
begin
-- if the current user is a member of a NT group and
-- NT group has access then is_srvrolemember()
-- and suser_id() will not work properly unless they
-- are invoked for current user
select @login = suser_name()
,@isloginsysadmin = is_srvrolemember(N'sysadmin')
,@login_id = suser_id()
end
else
begin
select @isloginsysadmin = is_srvrolemember(N'sysadmin',@login)
,@login_id = suser_id(@login)
end
-- Initialize
-- Set db context to distributor if HREPL
select @database = case when (@issqlpublisher = 1) then db_name() else @distribdb end
,@sid = suser_sid(@login,0)
,@fmergepub = 0
,@ftranpub = 0
,@skip = case when (@reserved = N'init') then 1 else 0 end
,@versionyukon = 0x900048B
--
-- We should always have a valid SID for any kind of login (provisioned or not)
-- If there is not valid SID then it is a bad login
--
if (@sid is null)
begin
raiserror(15007, 16, 1, @login)
return (1)
end
-- Existance check of the publication will be done in sp_MSpublication_access
-- Note, even if the login exists, it may or may not has access
-- to the server (granted or denied).
if @skip = 0
begin
-- Check if this database is published
IF sys.fn_MSrepl_ispublished(@database) != 1
BEGIN
-- The database is not published.
RAISERROR (18757, 16, -1)
RETURN 1
END
--
-- Permission check for CONNECT SQL
-- Skip this check when login_id is null
--
if @login_id is not null and
(exists (select * from sys.server_permissions where
class = 100 and major_id = 0 and minor_id = 0 and type = 'COSQ' and
grantee_principal_id = @login_id and state = 'D') or
not exists (select * from sys.server_permissions where
class = 100 and major_id = 0 and minor_id = 0 and type = 'COSQ' and
grantee_principal_id = @login_id and state in ('G','W')))
begin
raiserror(15007, 16, 2, @login)
return (1)
end

-- Don't do the user check if sysadmin since sysadmin can enter
-- the database as dbo.
if @isloginsysadmin = 0 and
not exists (select * from sys.database_principals u join sys.database_permissions p on u.principal_id = p.grantee_principal_id
where (u.sid = @sid or u.name = N'guest') and
p.class = 0 and p.major_id = 0 and p.minor_id = 0 and p.type = 'CO' and p.state in ('G','W') and
not exists (select * from sys.database_permissions d where d.class = 0 and d.major_id = 0 and d.minor_id = 0 and
d.grantee_principal_id = p.grantee_principal_id and d.type = 'CO' and d.state = 'D'))
begin
raiserror(20619, 16, -1, @login, @database)
return (1)
end
end
--
-- Get the publication id and see if what kind of publication it is
--
if object_id(N'dbo.sysmergepublications') is not NULL
begin
select @pubid = pubid from dbo.sysmergepublications where name = @publication and UPPER(publisher) = UPPER(@loc_publisher) and publisher_db = @database
if (@pubid is not null)
select @fmergepub = 1
end
if @fmergepub = 0 and object_id(N'dbo.syspublications') is not NULL
begin
if (@issqlpublisher = 1)
select @pubidtran = pubid from dbo.syspublications where name = @publication
else
select @pubidtran = pubid from sys.fn_IHgetpubid(@publication, @loc_publisher, @publisher_type)
if (@pubidtran is not null)
select @ftranpub = 1
end
--
-- publication should exist
--
if (@fmergepub = 0 and @ftranpub = 0)
begin
raiserror (20026, 16, -1, @publication)
return (1)
end
--
-- map the login to a user in the current db
--
select @user = name from sys.database_principals u where u.sid = @sid and
exists (select * from sys.database_permissions p where
p.class = 0 and p.major_id = 0 and p.minor_id = 0 and p.grantee_principal_id = u.principal_id and
p.type = 'CO' and p.state in ('G','W') and
not exists (select * from sys.database_permissions d where d.class = 0 and d.major_id = 0 and d.minor_id = 0 and
d.grantee_principal_id = p.grantee_principal_id and d.type = 'CO' and d.state = 'D'))
if @user is NULL
begin
--
-- The login is NON provisioned or
-- no explicit user mapping was found.
-- Find ways to get the user mapping
--
if @isloginsysadmin = 1
begin
--
-- the login is a NON provisioned sysadmin account
-- the user mapping is known
--
select @user = N'dbo'
end
else if @login_id is null
begin
--
-- NON provisioned non sysadmin account
-- impersonate the login and get the current user
-- there is no better way (server should have a better way)
--
select @distproc = N'EXECUTE AS LOGIN = N''' + REPLACE(@login, N'''', N'''''') + N''' '
+ N'select @p1 = user_name() '
+ N'REVERT '
exec @retcode = sys.sp_executesql @stmt = @distproc
,@params = N'@p1 sysname OUTPUT'
,@p1 = @user OUTPUT
if @@error <> 0
begin
--
-- could impersonate the non provisioned login
--
raiserror(20624, 16, 2, @login, @database)
return(1)
end
--
-- Now check if we have valid user
--
if @user is NULL
begin
--
-- the non provisioned login has no user access in the current db
--
raiserror(20624, 16, 3, @login, @database)
return(1)
end
end -- if @login_id is null
else
begin
--
-- the provisioned login has no user access in the current db
--
raiserror(20624, 16, 1, @login, @database)
return (1)
end
end
-- at this point we know that the login exists and has access to the publishing database
-- Add role if this SP is not being called from sp_addpublication/sp_addmergepublication
-- don't add role if 'init' because role cannot be added inside a transaction
if (@skip = 0)
begin
if (@ftranpub = 1)
begin
-- Tran specific processing
-- we will create just the role if needed
-- and skip fixing the the role
-- Role fixing should be explicitly done by calling sp_createtranpalrole
exec @retcode = sys.sp_MSrepl_FixTranPALRole @pubidtran, @publication, @publisher_type, @loc_publisher, @role output
end
else
begin
-- Merge specific processing
-- we will create just the role if needed
-- Fix the database role which represents the users who have access to this publication
exec @retcode = sys.sp_MSrepl_FixPALRole @pubid, @role output
end
if (@retcode<>0 or @@error <> 0)
return 1
--
-- add the user for the given login to the PAL role
-- we have to skip this in the 'init' stage because we may not have a role
-- explicitly call sp_grant_publication_access for any PAL user
-- no need to add if system dbo user
--
if @user != N'dbo'
begin
exec @retcode = sys.sp_addrolemember @role, @user
if (@retcode <> 0 or @@error <> 0)
return 1
exec @retcode = sys.sp_MSgrantconnectreplication @user
if (@retcode <> 0 or @@error <> 0)
return 1
end
end -- if (@skip = 0)
--
-- add login(s) to distributor
--
if (@skip = 1)
begin
--
-- publication init processing
-- choose all sysadmin logins + given login and prepare a info string for init
-- Skip logins that are not at the distributor without raising error during init
--
SELECT @distproc = QUOTENAME(RTRIM(@dist_rpcname)) + N'.' + QUOTENAME(RTRIM(@distribdb))
+ case when (isnull(@version,0) < @versionyukon) then N'.sys.sp_MSpublication_access'
else N'.sys.sp_MSinit_publication_access' end
,@initinfo = N''

declare #hC CURSOR LOCAL FAST_FORWARD for
select name from master.sys.syslogins
where hasaccess = 1
and (sid = @sid or sysadmin = 1)
for read only

open #hC
fetch #hC into @login2
while (@@fetch_status <> -1)
begin
if (isnull(@version,0) < @versionyukon)
begin
-- Pre-Yukon distributor
-- Call RPC to add login
EXEC @retcode = @distproc
@publisher = @loc_publisher,
@publisher_db = @database,
@publication = @publication,
@login = @login2,
@operation = N'add',
@skip = @skip
IF @@error <> 0 OR @retcode <> 0
return (1)
end
else
begin
-- Yukon or later version of distributor
-- build initinfo
select @initinfo = @initinfo + N'<item\>' + @login2 + N'</item\>'
end
fetch #hC into @login2
end
close #hC
deallocate #hC
--
-- One RPC call to Yukon or recent distributor with login initinfo
--
if (@initinfo != N'')
begin
EXEC @retcode = @distproc
@publisher = @loc_publisher,
@publisher_db = @database,
@publication = @publication,
@initinfo = @initinfo,
@skip = @skip
IF @@error <> 0 OR @retcode <> 0
return (1)
end
end
else
begin
--
-- choose given login for regular processing
--
SELECT @distproc = QUOTENAME(@dist_rpcname) + N'.' + QUOTENAME(@distribdb)
+ N'.sys.sp_MSpublication_access'
EXEC @retcode = @distproc
@publisher = @loc_publisher,
@publisher_db = @database,
@publication = @publication,
@login = @login,
@operation = N'add',
@skip = @skip
IF @@error <> 0 OR @retcode <> 0
return (1)
end
--
-- all done
--
return 0
END

No comments:

Post a Comment

Total Pageviews