Sql-server – Where is located sp_msreplcheck_publish stored procedure

sql servertransactional-replication

sp_msreplcheck_publish is a sp that checks if you have sufficient rights to run a statement to alter a published table (transactional replication).

Trying to alter a published table without elevated rights fires trigger tr_MStran_altertable that calls sp_MStran_ddlrepl that execute sp_msreplcheck_publish. It returns correctly :

Only members of the sysadmin fixed server role or db_owner fixed
database role can perform this operation.

The mystery for me is that I cannot find this sp in published database or in master. Is it generated on demand?

Best Answer

If is hidden from general view but, if you connect using a Dedicated Administrator Connection, you can see it in master via:

USE [master];
GO

SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_msreplcheck_publish'));

Not sure what you need to see the definition for, though, or why knowing where it is stored will help you accomplish anything. It's really this simple, and you can't change it, regardless of where it is stored:

create procedure sys.sp_MSreplcheck_publish 
as
begin
    --
    -- Do the sysadmin check first as it is 4 times more efficient
    -- than is_member call and reduces performance overhead in case 
    -- the user has sysadmin privileges
    --    
    if    is_srvrolemember('sysadmin') = 1 or
        is_member ('db_owner') = 1
    begin
        return (0)
    end
    else
    begin
        raiserror (21050, 14, -1)
        return (1)
    end
end
Related Question