Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_GrantSelectOnMergeSystemTables

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSrepl_GrantSelectOnMergeSystemTables (@pubid uniqueidentifier, @role sysname)
AS
    declare @quotedrole nvarchar(258)

    select @quotedrole = quotename(@role)

    -- grant select on system tables to the given role
    exec ('grant select on dbo.MSmerge_errorlineage to ' +  @quotedrole)
    if (@@error <> 0)
        return 1
    exec ('grant select on dbo.MSmerge_identity_range to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.sysmergepublications to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.sysmergearticles to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.sysmergesubscriptions to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.MSmerge_replinfo to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.MSmerge_tombstone to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.MSmerge_contents to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.MSmerge_genhistory to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.MSmerge_conflicts_info to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.sysmergeschemachange to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.sysmergesubsetfilters to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select,insert on dbo.MSmerge_partition_groups to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.MSmerge_generation_partition_mappings to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.MSmerge_current_partition_mappings to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.MSmerge_past_partition_mappings to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.MSmerge_dynamic_snapshots to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.sysmergepartitioninfo to ' + @quotedrole)
    if (@@error <> 0)
        return 1
    exec('grant select on dbo.sysmergepartitioninfoview to ' + @quotedrole)
    if (@@error <> 0)
        return 1

    -- cursor to browse through all articles in this publication and grant
    -- permissions on conflict_table, before_image_table etc to the role
    declare @bi_table sysname
    declare @bi_objectid int
    declare @owner sysname
    declare @quotedname nvarchar(517)

    declare art_cursor cursor LOCAL FAST_FORWARD
    for select before_image_objid from dbo.sysmergearticles where pubid = @pubid
    open art_cursor
    fetch art_cursor into @bi_objectid
    while (@@fetch_status <> -1)
    begin
        if @bi_objectid is not NULL
        begin
            select @owner = NULL
            select @owner = schema_name(schema_id) from sys.objects where object_id = @bi_objectid
            if @owner is NULL
                return 1

            select @bi_table = OBJECT_NAME(@bi_objectid)
            if (@@error <> 0)
                return 1

            select @quotedname = QUOTENAME(@owner)+ '.' + QUOTENAME(@bi_table)
            exec('grant select (system_delete, generation), update(generation), delete on ' + @quotedname + ' to ' + @quotedrole)
            if (@@error <> 0)
                return 1
        end
        select @bi_objectid = NULL
        fetch art_cursor into @bi_objectid
    end
    close art_cursor
    deallocate art_cursor


 
Last revision 2008RTM
See also

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