-- Name:
--          sp_MSrepl_DistributorPALAccess

-- Description:

--          This stored procedure runs in any database at a distributor.  It returns
--          Success (0) if the current user has PAL access to distribution related
--          information at the distributor.  Access is granted to those in the
--          db_owner or replmonitor role of the/any distribution database.  Access is
--          also granted to those in the PAL of the/any publication of the/any publisher
--          that makes use of the/any distribution database at this distributor.

--          If @distribution_db is specified, only this distribution database is checked.
--          If @distribution_db is not specified, all distribution databases are checked.

--          If @publisher is specified, only this publisher is checked.
--          If @publisher is not specified, all publishers are checked.

--          If @publication is specified, only this publication is checked.
--          If @publication is not specified, all publications are checked.

--          If @publication is specified, @publisher must be specified as well.

-- Security:
--          Internal
-- Requires Certificate signature for catalog access

-- Returns:
--          Success (0) or failure (1)

-- Owner:
--          

create procedure sys.sp_MSrepl_DistributorPALAccess(
    @distribution_db sysname = '%',
    @publisher sysname = '%',
    @publication sysname = '%'
) AS

    declare
         @db_name sysname
        ,@has_access bit
        ,@cmd nvarchar(200)
        ,@retcode int

    select @has_access = 1

    
    -- Search sysdababases for distribution databases
    
    declare hCdatabase CURSOR LOCAL FAST_FORWARD FOR
        select dist.name
              from master.dbo.sysdatabases d
        inner join msdb.dbo.MSdistributiondbs dist
                on dist.name = d.name collate database_default
        inner join msdb.dbo.MSdistpublishers p
                on p.distribution_db = dist.name collate database_default
             where dist.name like @distribution_db collate database_default
               and UPPER(p.name collate database_default) LIKE UPPER(@publisher) collate database_default
               and has_dbaccess(d.name) = 1

    open hCdatabase
    fetch next from hCdatabase into @db_name
    while (@@fetch_status <> -1 and @has_access = 1)
    begin
        
        -- Check to see if the user has PAL access to the distribution database.
        
        begin
            -- Check 'db_owner' and 'replmonitor' roles in distribution database or
            -- determine whether the user is in the PAL for any publication associated
            -- with this distribution database
            select @cmd = quotename(@db_name) + N'.sys.sp_MSrepl_DistDBPALAccess'
            exec @retcode = @cmd @publisher = @publisher,
                @publication = @publication
            if @retcode = 0 and @@error = 0
                select @has_access = 0
        end
        fetch next from hCdatabase into @db_name
    end
    close hCdatabase
    deallocate hCdatabase
    return(@has_access)