Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_FixTranPALRole

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSrepl_FixTranPALRole
(
    @pubid int
    ,@publication sysname
    ,@publisher_type sysname
    ,@publisher sysname
    ,@role sysname output
    ,@reserved bit = 0 -- 1 = repair
)
AS
BEGIN
    set nocount on
    declare @retcode int
                ,@publisher_db sysname
                ,@dbwiderole sysname
                ,@rpcsrvname sysname
                ,@distribdb sysname
                ,@distproc nvarchar(300)
                ,@login sysname
                ,@user sysname


     declare @createrole nvarchar(200)

    
    -- initialize
    
    select @role = N'MSReplPAL_' + cast(db_id() as nvarchar(10)) + N'_' + cast(@pubid as nvarchar(10))
            ,@dbwiderole = 'MStran_PAL_role'
    
    -- check for the db wide pal role first
    
    if not exists (select * from sys.database_principals where name=@dbwiderole and type = 'R')
    begin
        select @createrole = 'CREATE ROLE ' + @dbwiderole
        exec @retcode = sys.sp_executesql @createrole
        if (@retcode <> 0 or @@error <> 0)
            return 1
    end
    
    -- create the role as needed
    
    if exists (select * from sys.database_principals where name=@role and type = 'R')
    begin
        
        -- check if this role is there in the db wide pal role.
        
        if not exists (select 1 from sys.database_role_members r
                            inner join sys.database_principals ur on r.role_principal_id = ur.principal_id
                            inner join sys.database_principals uu on r.member_principal_id = uu.principal_id
                            where ur.name = @dbwiderole
                            and uu.name = @role)
        begin
            exec @retcode = dbo.sp_addrolemember @dbwiderole, @role
            if (@retcode <> 0 or @@error <> 0)
                return 1
        end
    end -- role exists
    else
    begin -- role does not exist
        
        -- create a new role
        
        select @createrole = 'CREATE ROLE ' + @role
        exec @retcode = sys.sp_executesql @createrole
        if (@retcode <> 0 or @@error <> 0)
            return 1
        
        -- add it to the dbwide role
        
        exec @retcode = dbo.sp_addrolemember @dbwiderole, @role
        if (@retcode <> 0 or @@error <> 0)
            return 1
        
        -- Skip this unless we are fixing this role
        
        if (@reserved = 1)
        begin
            
            -- check if there are any users already been granted access to on the
            -- distributor for this publication
            -- using a temp table because of the following error
            -- EXECUTE cannot be used as a source when inserting into a table variable.
            
            create table #logins_table (login sysname)

            exec @retcode = dbo.sp_MSrepl_getdistributorinfo @publisher = @publisher, @rpcsrvname = @rpcsrvname OUTPUT, @distribdb = @distribdb OUTPUT
            IF ((@retcode <> 0) or (@@error <> 0) OR (@distribdb IS NULL) OR (@rpcsrvname IS NULL))
            BEGIN
                RAISERROR (14071, 16, -1)
                return(1)
            END

            select @publisher_db = case when (@publisher_type = N'MSSQLSERVER') then db_name() else @distribdb end

            select @distproc = QUOTENAME(RTRIM(@rpcsrvname)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSenumerate_PAL'
            insert into #logins_table exec @distproc
                    @publisher = @publisher,
                    @publisher_db = @publisher_db,
                    @publication = @publication
            if @@error <> 0
                return 1

            declare #logins_cursor cursor LOCAL FAST_FORWARD for
                select login from #logins_table

            open #logins_cursor
            fetch #logins_cursor into @login
            while (@@fetch_status <> -1)
            begin
                select @user = NULL
                select @user = name from sys.database_principals u
                where u.sid = suser_sid(@login, 0) and u.name <> N'dbo'
                    and
                        exists (select * from sys.database_permissions p
                                where p.class = 0 and p.major_id = 0
                                    and p.minor_id = 0 and p.grantee_principal_id = u.principal_id
                                    and p.type = 'CO' and p.state in ('G','W'))
                                    and not exists (select * from sys.database_permissions p
                                                where p.class = 0 and p.major_id = 0
                                                    and p.minor_id = 0
                                                    and p.grantee_principal_id = u.principal_id
                                                    and p.type = 'CO' and p.state = 'D') -- connect permission not denied
                if @user is not NULL
                begin
                    exec @retcode = dbo.sp_addrolemember @role, @user
                    if (@retcode <> 0 or @@error <> 0)
                        return 1
                end
                fetch #logins_cursor into @login
            end
            close #logins_cursor
            deallocate #logins_cursor
        end -- if (@reserved = 1)

    end -- role does not exist
    
    -- all done
    
    return 0
END

 
Last revision 2008RTM
See also

  sp_createpalrole (Procedure)
sp_grant_publication_access (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