Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSreplcheck_pull

  No additional text.


Syntax
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

 
Last revision 2008RTM
See also

  sp_addmergesubscription (Procedure)
sp_check_publication_access (Procedure)
sp_cleanmergelogfiles (Procedure)
sp_helpmergelogfiles (Procedure)
sp_helpmergelogfileswithdata (Procedure)
sp_helpmergelogsettings (Procedure)
sp_helpmergesubscription (Procedure)
sp_IHhelparticle (Procedure)
sp_IHhelppublication (Procedure)
sp_link_publication (Procedure)
sp_mergesubscriptionsummary (Procedure)
sp_MSgetarticlereinitvalue (Procedure)
sp_MSgettrancftsrcrow (Procedure)
sp_MSgettranconflictrow (Procedure)
sp_MSget_synctran_commands (Procedure)
sp_MShelpconflictpublications (Procedure)
sp_MShelptranconflictcounts (Procedure)
sp_MSmaketrancftproc (Procedure)
sp_MSmerge_is_snapshot_required (Procedure)
sp_MSrepl_addsubscription (Procedure)
sp_MSrepl_agentstatussummary (Procedure)
sp_MSrepl_distributionagentstatussummary (Procedure)
sp_MSrepl_enumpublications (Procedure)
sp_MSrepl_helparticle (Procedure)
sp_MSrepl_helparticlecolumns (Procedure)
sp_MSrepl_helppublication (Procedure)
sp_MSrepl_helpsubscriberinfo (Procedure)
sp_MSrepl_mergeagentstatussummary (Procedure)
sp_MSrepl_publicationsummary (Procedure)
sp_MSrepl_subscriptionsummary (Procedure)
sp_MSreset_queued_reinit (Procedure)
sp_replsetoriginator (Procedure)
sp_script_synctran_commands (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash