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