Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.


-- 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

         @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)
        -- Check to see if the user has PAL access to the distribution database.
            -- 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
        fetch next from hCdatabase into @db_name
    close hCdatabase
    deallocate hCdatabase

Last revision 2008RTM
See also

  sp_helpdistributor_properties (Procedure)
sp_MSdrop_replcom (Procedure)
sp_MShelp_distdb (Procedure)


  Query a named instance
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
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash