April 30, 2012

sp_link_publication (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_link_publication(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, int @security_mode
, nvarchar @login
, nvarchar @password
, nvarchar @distributor)

MetaData:

 create procedure sys.sp_link_publication   
(
@publisher sysname, -- publishing server name or linked entry name for publisher
@publisher_db sysname, -- publishing database name
@publication sysname, -- publication name
@security_mode int, -- 0 = standard; 1 = integrated; 2 = static linked server entry
@login sysname = null, -- only needed for standard mode
@password sysname = null,-- only needed for standard mode
@distributor sysname = @publisher
)
as
begin
set nocount on
declare @retcode int
,@publisherlink sysname
,@rpcproc nvarchar(4000)
,@islocalpub bit
,@existingsecuritymode int
,@contextuser sysname
,@fcreateduniquelink bit
,@fcreateduser bit
,@fprepcertonsub bit
,@fgrantcertontarget bit
,@netname sysname
,@certname sysname
,@trigcount int
,@palcheck_retcode int
,@regencmd nvarchar(1000)
,@dbname sysname
,@pubversion varbinary(16)
,@publisherlinkusertodrop sysname

--
-- Security Check
--
exec @retcode = sys.sp_MSreplcheck_subscribe
if @@error <> 0 or @retcode <> 0
return (1)
--
-- Parameter check: @security_mode
--
if @security_mode not in (0,1,2)
begin
raiserror(21055, 16, -1, '@security_mode','sp_link_publication')
return (1)
end
--
-- SQL Authenticated linked entry - login cannot be null
--
if (@security_mode = 0 and @login is null)
begin
raiserror(21055, 16, -1, '@login','@security_mode = 0')
return (1)
end
--
-- Create tables if necessary
--
if object_id('dbo.MSreplication_objects') is null or object_id('dbo.MSsubscription_properties') is null
begin
exec @retcode = sys.sp_MScreate_sub_tables_internal @tran_sub_table = 1
if @@error <> 0 or @retcode <> 0
return 1
-- raiserror(20588, 16, -1)
-- return 1
end

--
-- Call cleanup scripts to remove the old linked server (if exists), related triggers
-- and certificates/logins/users.
--
exec @retcode = sys.sp_unlink_publication_internal
@publisher = @publisher
,@publisher_db = @publisher_db
,@publication = @publication
,@publisherlinkusertodrop = @publisherlinkusertodrop output

if @@ERROR <> 0 or @retcode <> 0
return 1

--
-- We can only drop the user, if there are no synctran triggers
-- due to dependancy issues. We can't drop the triggers at this point
-- since we could run into an issue when the sp errors out before the
-- triggers are created where DML would be allowed on the publication's tables.
-- Thus we only attempt to drop the user when there are no sync triggers.
--
select @trigcount = isnull(count(object_name),0)
from dbo.MSreplication_objects
where upper(publisher) = @publisher
and publisher_db = @publisher_db
and publication = @publication
and object_type = 'T'


if @trigcount = 0 and @publisherlinkusertodrop is not null
BEGIN
exec @retcode = sys.sp_dropuser @name_in_db = @publisherlinkusertodrop
if @@error <> 0 or @retcode <> 0
return (1)
END

--
-- initialize
--
select @publisher = upper(@publisher)
,@distributor = upper(@distributor)
,@password = case when (@password = N'') then null else @password end
,@publisher_db = isnull(@publisher_db, db_name())
,@islocalpub = case when (upper(@publisher) = upper(@@servername)) then 1 else 0 end
,@fcreateduniquelink = 0
,@fprepcertonsub = 0
,@fgrantcertontarget = 0
--
-- Check for an entry in MSsubscription_properties that
-- was created uniquely for this subscription by sp_link_publication
-- should have the prefix as defined
--
select @publisherlink = publisherlink
,@existingsecuritymode = publisher_security_mode
from MSsubscription_properties
where upper(publisher) = @publisher
and publisher_db = @publisher_db
and publication = @publication

--
-- process based on security_mode
--
if @security_mode in (0,1)
begin
--
-- Standard or Integrated
--
if (@islocalpub = 1)
begin
--
-- local publisher - we will not use linked server
-- for security_modes 0 and 1. Set publisher link
-- to servername for reference
--
select @publisherlink = @publisher
if @security_mode = 0
begin
--
-- Special processing for standard security
-- the user for publiser db for given login for SQL authentication
-- will have a proxy user in subscriber db with db_owner privileges
-- get the user in publisher db
--
if (ISNULL(IS_SRVROLEMEMBER('sysadmin'),@login) = 1)
begin
--
-- special case for sysadmin users
-- map contextuser to dbo
--
select @contextuser = 'dbo'
end
else
begin
select @rpcproc =
N'select @p1 = a.name from ' + quotename(@publisher_db)
+ N'.sys.database_principals as a join master.dbo.syslogins as b on a.sid = b.sid where b.loginname = @p2'
exec @retcode = sys.sp_executesql @stmt = @rpcproc
,@params = N'@p1 sysname output, @p2 sysname'
,@p1 = @contextuser output
,@p2 = @login
end
--
-- valid pubdb user should exist
--
if @contextuser is null
begin
raiserror(21855, 16, 1, @login, @publisher_db)
return (1)
end
--
-- for sysadmin logins and the case when the user for the publisher login does not exist on
-- this subscriber db - we will use proxy
-- for other case when user for the publisher login exists on the subscriber db we will use it
--
if (lower(@contextuser) = 'dbo') or
not exists (select * from sys.database_principals as a join master.dbo.syslogins as b
on a.sid = b.sid
where a.name = @contextuser and b.loginname = @login)
begin
--
-- we will use a proxy user on both publisher db and subscriber db to avoid SA escalation
--
exec @retcode = sys.sp_MSprocesspublisherlink @mode = 1, @islocalpub = @islocalpub, @securitymode = @security_mode
if @@error <> 0 or @retcode <> 0
return(1)
--
-- we will also create the proxy user on publishing db
--
select @rpcproc = quotename(@publisher_db) + N'.sys.sp_MSprocesspublisherlink'
exec @retcode = @rpcproc @mode = 1, @islocalpub = @islocalpub, @securitymode = @security_mode
if @@error <> 0 or @retcode <> 0
return(1)
select @contextuser = N'repllinkproxy'
,@login = N'repllinkproxy' -- override user specified local login
,@fcreateduser = 1
end
end -- @security_mode = 0
end -- (@islocalpub = 1)
else
begin -- (@islocalpub = 0)
--
-- Remote publisher - we will create a unique linked server entry for this subscription
-- if there is one existing we will drop and recreate it
--
select @publisherlink = sys.fn_MSrepllinkname(N'REPLLINK', @publisher, @publisher_db, @publication, db_name())
--
-- Refresh the linked entry
--
exec @retcode = sys.sp_MSprocesspublisherlink 1, @islocalpub, @security_mode, @publisherlink, @publisher, @login, @password
if @@error <> 0 or @retcode <> 0
return(1)
--
-- we have predefined user context for SQL authentication
--
select @contextuser = N'repllinkproxy'
,@login = N'repllinkproxy' -- local login will always be this
,@fcreateduniquelink = 1
,@fcreateduser = 1
end -- (@islocalpub = 0)
end
else
begin
--
-- static linked server case
--
if (@existingsecuritymode in (0,1) and @islocalpub = 0)
begin
--
-- we might have a linked entry that was created for standard/integrated mode
-- drop this linked entry
--
select @publisherlink = sys.fn_MSrepllinkname(N'REPLLINK', @publisher, @publisher_db, @publication, db_name())
exec sys.sp_MSprocesspublisherlink 2, @islocalpub, @existingsecuritymode, @publisherlink, @publisher
end
--
-- publisher should exist in sys.servers
--
select @publisherlink = name
,@netname = upper(data_source)
from sys.servers
where upper(name) = @publisher
if (@publisherlink is null)
begin
raiserror(20620, 16, -1, @publisher)
return (1)
end
--
-- Adjust publisher name as necessary
--
if (@netname != @publisher)
begin
select @publisher = @netname
end
end

--
-- Do PAL validation on local or remote publisher db
-- Build a command string that can be executed on the context of
-- the security mode and on a given link if necessary.
-- This also avoids direct usage of REVERT which
-- is not supported on 80 compatible databases
--
select @rpcproc = N'declare @cmd nvarchar(4000) '
+ N'declare @abc table( rc int ) '
+ N'select @cmd = N''declare @retcode int exec @retcode = '
+ sys.fn_replreplacesinglequote(quotename(@publisher_db))
+ N'.dbo.sp_MSreplcheck_pull @publication = N'''''
+ replace(@publication, N'''', N'''''''''')
+ N''''' select @retcode'' '
+ N'insert into @abc(rc) '
+ N'execute (@cmd) '
--
-- for security mode 0, execute in proxy login context
--
if (@security_mode = 0)
begin
select @rpcproc = @rpcproc
+ N'as login = N'''
+ sys.fn_replreplacesinglequote(@login) + N''' '
end
--
-- For remote publisher specify the link where the execution needs to happen
-- This validates the linked entry and ensures
-- proper processing for resource SPs on publisher
--
if (@islocalpub = 0)
begin
select @rpcproc = @rpcproc
+ N'at '
+ quotename(@publisherlink)
+ N' select @palrc = rc from @abc '
end
--
-- execute this command under proper user context
-- and get PAL check return code
--
exec @retcode = sp_executesql
@stmt = @rpcproc
,@params = N'@palrc int output'
,@palrc = @palcheck_retcode output
if @@error <> 0 or @retcode <> 0 or @palcheck_retcode <> 0
goto UNDO


--
-- RPC to get commands needed to regenerate subscriber triggers
--
create table #tab1 ( cmd nvarchar(4000) )
select @dbname = db_name()

--
-- Check to see if initial sync is done, if not skip trigger regeneration
--
declare @num_dup_rows int
,@agent_id int
select @agent_id = avg(id), @num_dup_rows = count(*) from MSsubscription_agents where
UPPER(publisher) = UPPER(@publisher) and
publisher_db = @publisher_db and
-- we know that we have independent agents (enforced in
-- sp_addpublication) so specify the publication name
publication = @publication and
-- We know the subscription must be updateble. This
-- is to reduce the chance of dup rows.
update_mode <> 0

if @agent_id is not null and @num_dup_rows <= 1
begin
create table #ver (version varbinary(16))
declare @cmd nvarchar(500)
select @cmd = N'execute (N''select @@microsoftversion'')'
if (@security_mode = 0)
select @cmd = @cmd + N'as login = N''' + sys.fn_replreplacesinglequote(@login) + N''' '
if (@islocalpub = 0)
select @cmd = @cmd + N' at ' + QUOTENAME(@publisherlink)

insert into #ver exec sp_executesql @stmt=@cmd
select @pubversion = version from #ver
drop table #ver

select @regencmd = ''

if (@islocalpub != 0 or @pubversion > 0x09000577)
begin
if (@islocalpub = 0)
begin
select @regencmd = QUOTENAME(@publisherlink) + N'.'
end

select @regencmd = @regencmd
+ QUOTENAME(@publisher_db)
+ N'.dbo.sp_script_synctran_commands N'''
+ sys.fn_replreplacesinglequote(@publication)
+ N''', N''all'', 1'

if (@security_mode = 0)
insert into #tab1 exec (@regencmd) as login = N'repllinkproxy'
else
insert into #tab1 exec (@regencmd)
end
else -- not possible to fall through on a local case
begin
select @regencmd = QUOTENAME(@publisherlink)
+ N'.' + QUOTENAME(@publisher_db)
+ N'.dbo.sp_script_synctran_commands N'''
+ sys.fn_replreplacesinglequote(@publication)
+ N''', N''all'''

if (@security_mode = 0)
insert into #tab1 exec (@regencmd) as login = N'repllinkproxy'
else
insert into #tab1 exec (@regencmd)

delete from #tab1 WHERE cmd NOT LIKE N'if (@@microsoftversion >= 0x080002C0) begin exec sp_addsynctriggers%' COLLATE database_default

declare @offset_length int
select @offset_length = LEN(N'if (@@microsoftversion >= 0x080002C0) begin exec ') + 2
update #tab1 set cmd = SUBSTRING(cmd, 0, @offset_length) + N'sys.' + SUBSTRING(cmd, @offset_length, LEN(cmd)-@offset_length-3) + N',1 end'
-- tack the extra parameter on the end, and add sys. in front of the SP name
end
end
BEGIN TRAN
--
-- Now store the linked server information in MSsubscription_properties
--
if exists (select * from MSsubscription_properties
where upper(publisher) = @publisher
and publisher_db = @publisher_db
and publication = @publication)
begin
update MSsubscription_properties set
publisher_login = null,
publisher_password = null,
publisher_security_mode = @security_mode,
publisherlink = case when (@security_mode in (0,1)) then N'xxxx' else @publisherlink end,
publisherlinkuser = case when (@security_mode in (0,1) and @contextuser = N'repllinkproxy') then N'xxxx' else @contextuser end,
distributor = isnull(@distributor, distributor)
where upper(publisher) = @publisher
and publisher_db = @publisher_db
and publication = @publication
end
else
begin
insert into MSsubscription_properties (publisher, publisher_db, publication, publication_type,
publisher_security_mode, distributor, distributor_security_mode, publisherlink, publisherlinkuser)
values (@publisher, @publisher_db, @publication, 0,
@security_mode, ISNULL(@distributor, @publisher), 1,
case when (@security_mode in (0,1)) then N'xxxx' else @publisherlink end,
case when (@security_mode in (0,1) and @contextuser = N'repllinkproxy') then N'xxxx' else @contextuser end)
end
--
-- Check for errors in update/insert
--
if @@error <> 0
BEGIN
rollback tran
goto UNDO
END

--
-- recreate trigger based on security mode
-- get the trigger definition
--

exec @retcode = xp_execresultset N'select cmd from #tab1', @dbname
if (@retcode != 0 or @@error <> 0)
begin
rollback tran
goto UNDO
end

drop table #tab1

--
-- get count of triggers
--
select @trigcount = isnull(count(object_name),0)
from dbo.MSreplication_objects
where upper(publisher) = @publisher
and publisher_db = @publisher_db
and publication = @publication
and object_type = 'T'

--
-- certificate signing stage
-- proceed only if we have triggers available
--
if (@security_mode = 0) and (@trigcount >= 3) and (@trigcount % 3 = 0)
begin
--
-- Create a well known certificate on subdb
--
select @certname = N'REPLCERT_' + db_name() + cast(newid() as sysname)
exec @retcode = sys.sp_MSrepltrigpreparecert @mode = 1
,@certname = @certname
,@publisher = @publisher
,@publisher_db = @publisher_db
,@publication = @publication
if @@error != 0 or @retcode != 0
begin
rollback tran
goto UNDO
end
select @fprepcertonsub = 1
--
-- grant privileges necessary
--
if (@islocalpub = 0)
begin
select @rpcproc = N'master.sys.sp_MSrepltrigcertgrant'
exec @retcode = @rpcproc @mode = 1
,@islocalpub = @islocalpub
,@certname = @certname
,@targetdb = N'master'
end
else
begin
select @rpcproc = quotename(@publisher_db) + N'.sys.sp_MSrepltrigcertgrant'
exec @retcode = @rpcproc @mode = 1
,@islocalpub = @islocalpub
,@certname = @certname
,@targetdb = @publisher_db
end
if @@error != 0 or @retcode != 0
begin
rollback tran
goto UNDO
end
select @fgrantcertontarget = 1
end -- if (@security_mode = 0) and (@trigcount >= 3) and (@trigcount % 3 = 0)

commit tran
--
-- all done
--
return 0

UNDO:
--
-- clean up certificates if they are created, granted on target
-- drop the link if it has been created
-- drop local user if created for local publisher
--
if (@fgrantcertontarget = 1)
begin
if (@islocalpub = 0)
begin
select @rpcproc = N'master.sys.sp_MSrepltrigcertgrant'
exec @rpcproc @mode = 2
,@islocalpub = @islocalpub
,@certname = @certname
,@targetdb = N'master'
end
else
begin
select @rpcproc = quotename(@publisher_db) + N'.sys.sp_MSrepltrigcertgrant'
exec @rpcproc @mode = 2
,@islocalpub = @islocalpub
,@certname = @certname
,@targetdb = @publisher_db
end
end
if (@fprepcertonsub = 1)
begin
exec sys.sp_MSrepltrigpreparecert @mode = 2
,@certname = @certname
,@publisher = @publisher
,@publisher_db = @publisher_db
,@publication = @publication
end
if (@fcreateduniquelink = 1)
begin
exec @retcode = sys.sp_MSprocesspublisherlink @mode = 2
,@islocalpub = @islocalpub, @securitymode = @security_mode
,@linkname = @publisherlink, @remserver = @publisher
end
if (@fcreateduser = 1)
begin
exec sys.sp_dropuser @name_in_db = @contextuser
end
--
-- return error
--
return 1
end

No comments:

Post a Comment

Total Pageviews