2

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?

3

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
  • Thank you. My question is answered and my curiosity satisfied now that I know that there are hidden sp and the way to see them. – Filoo Apr 29 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.