Skip to main content

Setting up a non-sysadmin user in a standalone environment

ON THIS PAGE

Setting up a non-sysadmin user in a standalone environment

The following section describes how to set up Microsoft SQL Server to allow a non-sysadmin user to be specified in the Replicate Microsoft SQL Server endpoint connection settings.

Note:

If you wish to set up a non-sysadmin user in an AlwaysOn environment, see Setting up a non-sysadmin user when working with AlwaysOn availability groups.

Limitations and considerations

To support a non-sysadmin Replicate user:

  1. Set up Microsoft SQL Server for Replication as described in Setting up Microsoft SQL Server for replication.

  2. Enable MS-REPLICATION on the source database. This can either be done manually or by running the task once using a sysadmin user.

    Note:

    MS-REPLICATION distributor should either be configured as local or in a way that allows access to non-sysadmin users via the associated linked server.

  3. If the Exclusively use sp_repldone within a single task endpoint option is enabled, stop the MS-REPLICATION Log Reader job.
  4. Create schema [attrep] in the Master DB.
  5. Create the table valued function [attrep].[split_partition_list] on the Master database, as follows:

    USE [master]
    GO
    
    set ansi_nulls on
    go
    
    set quoted_identifier on
    go
    
    if (object_id('[attrep].[split_partition_list]','TF')) is not null
    drop  function [attrep].[split_partition_list];
    go
    
    create function [attrep].[split_partition_list] 
    ( 
    @plist varchar(8000),	--A delimited list of partitions	
    @dlm nvarchar(1)	--Delimiting character
    ) 
    returns @partitionsTable table --Table holding the BIGINT values of the string fragments
    (
    pid bigint primary key
    ) 
    as 
    begin
    declare @partition_id bigint;
    declare @dlm_pos integer;
    declare @dlm_len integer;
    
    set @dlm_len = len(@dlm);
    
    while (charindex(@dlm,@plist)>0)
    begin 
    set @dlm_pos = charindex(@dlm,@plist);
    set @partition_id = cast( ltrim(rtrim(substring(@plist,1,@dlm_pos-1))) as bigint);
    insert into @partitionsTable (pid) values (@partition_id)
    set @plist = substring(@plist,@dlm_pos+@dlm_len,len(@plist));
    end 
    set @partition_id = cast (ltrim(rtrim(@plist)) as bigint);
    insert into @partitionsTable (pid) values (  @partition_id  );
    return
    end
    GO
  6. Create procedure [attrep].[rtm_dump_dblog] on the Master database, as follows:

    USE [master]
    GO
    
    use [MASTER] 
    go 
    
    if (object_id('[attrep].[rtm_dump_dblog]','P')) is not null
    drop procedure [attrep].[rtm_dump_dblog]; 
    go
    
    set ansi_nulls on
    go 
    
    set quoted_identifier on 
    go
    
    create procedure [attrep].[rtm_dump_dblog]
    (
    @start_lsn        varchar(32),
    @seqno            integer,
    @filename         varchar(260),
    @partition_list        varchar(8000), -- A comma delimited list: P1,P2,... Pn
    @programmed_filtering integer,
    @minPartition     bigint,
    @maxPartition     bigint
    ) 
    as begin
    
            declare @start_lsn_cmp varchar(32); -- Stands against the GT comparator
    
            SET NOCOUNT ON  -- Disable "rows affected display"
    
            set @start_lsn_cmp = @start_lsn;
            if (@start_lsn_cmp) is null
                    set @start_lsn_cmp = '00000000:00000000:0000';
    
            if (@partition_list is null)
            begin
                    RAISERROR ('Null partition list waspassed',16,1);
                    return
                    --set @partition_list = '0,';    -- A dummy which is never matched
            end
    
            if (@start_lsn) is not null
                    set @start_lsn = '0x'+@start_lsn;
    
    if (@programmed_filtering=0)
            SELECT
                    [Current LSN],
                    [operation],
                    [Context],
                    [Transaction ID],
                    [Transaction Name],
                    [Begin Time],
                    [End Time],
                    [Flag Bits],
                    [PartitionID],
                    [Page ID],
                    [Slot ID],
                    [RowLog Contents 0],
                    [Log Record],
                    [RowLog Contents 1] -- After Image
            FROM
                    fn_dump_dblog (
                            @start_lsn, NULL, N'DISK', @seqno, @filename,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default)
            where [Current LSN] collate SQL_Latin1_General_CP1_CI_AS > @start_lsn_cmp collate SQL_Latin1_General_CP1_CI_AS -- This aims for implementing FN_DBLOG based on GT comparator.
            and
            (
              (  [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') )
              or
              (  [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW')
                    and
                    ( ( [context]   in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX' and  (datalength([RowLog Contents 0]) in (0,14,28))) )   -- This one filters only TEXT_MIX of interest.\
                    and       [PartitionID] in ( select * from master.attrep.split_partition_list (@partition_list,','))
              )
              or
              ([operation] = 'LOP_HOBT_DDL')
            )
    else
            SELECT
                    [Current LSN],
                    [operation],
                    [Context],
                    [Transaction ID],
                    [Transaction Name],
                    [Begin Time],
                    [End Time],
                    [Flag Bits],
                    [PartitionID],
                    [Page ID],
                    [Slot ID],
                    [RowLog Contents 0],
                    [Log Record],
                    [RowLog Contents 1] -- After Image
            FROM
                    fn_dump_dblog (
                            @start_lsn, NULL, N'DISK', @seqno, @filename,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default,
                            default, default, default, default, default, default, default)
            where [Current LSN] collate SQL_Latin1_General_CP1_CI_AS > @start_lsn_cmp collate SQL_Latin1_General_CP1_CI_AS -- This aims for implementing FN_DBLOG based on GT comparator.
            and
            (
              (  [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') )
              or
              (  [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW')
                    and
                    ( ( [context]   in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX' and  (datalength([RowLog Contents 0]) in (0,14,28))) )   -- This one filters only TEXT_MIX of interest.\
                    and           ([PartitionID] is not null) and ([PartitionID] >= @minPartition and [PartitionID]<=@maxPartition)
              )
              or
              ([operation] = 'LOP_HOBT_DDL')
            )
            SET NOCOUNT OFF -- Re-enable "rows affected display"
    
    end
    GO
  7. Create certificate on Master DB:

    Use [master]
    Go
    CREATE CERTIFICATE [attrep_rtm_dump_dblog_cert]
    ENCRYPTION BY PASSWORD = N'choose_your_own_pwd'
    WITH SUBJECT = N'Certificate for FN_DUMP_DBLOG Permissions';
  8. Create login from the certificate, as follows:

    Use [master]
    Go
    CREATE LOGIN attrep_rtm_dump_dblog_login FROM CERTIFICATE 
    [attrep_rtm_dump_dblog_cert];
  9. Add the login to the sysadmin server role as follows:

    ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_dump_dblog_login];
  10. Add the certificate signature to [master].[attrep].[rtm_dump_dblog], as follows:

    Use [master]
    GO
    ADD SIGNATURE
    TO [master].[attrep].[rtm_dump_dblog]
    BY CERTIFICATE [attrep_rtm_dump_dblog_cert]
    WITH PASSWORD = 'choose_your_own_pwd';
    Note:

    If the stored procedure is recreated, you need to add the signature again.

  11. Create procedure [attrep].[rtm_position_1st_timestamp] on the Master database, as follows:

    use [master]
    if object_id('[attrep].[rtm_position_1st_timestamp]','P') is not null
    DROP PROCEDURE [attrep].[rtm_position_1st_timestamp];
    go
    create procedure [attrep].[rtm_position_1st_timestamp]
    (
    @dbname                sysname,      -- Database name
    @seqno                 integer,      -- Backup set sequence/position number within file
    @filename              varchar(260), -- The backup filename
    @1stTimeStamp          varchar(40)   -- The timestamp to position by
    ) 
    as begin
    
    SET NOCOUNT ON       -- Disable "rows affected display"
    
    declare @firstMatching table
    (
    cLsn varchar(32),
    bTim datetime
    )
    
    declare @sql nvarchar(4000)
    declare @nl                       char(2)
    declare @tb                       char(2)
    declare @fnameVar                 sysname = 'NULL'
    
    set @nl  = char(10); -- New line
    set @tb  = char(9)   -- Tab separator
    
    if (@filename is not null)
    set @fnameVar = ''''+@filename +''''
    
    set @sql='use ['+@dbname+'];'+@nl+
    'select top 1 [Current LSN],[Begin Time]'+@nl+
    'FROM fn_dump_dblog (NULL, NULL, NULL, '+ cast(@seqno as varchar(10))+','+ @fnameVar+','+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default)'+@nl+
    'where operation=''LOP_BEGIN_XACT''' +@nl+
    'and [Begin Time]>= cast('+''''+@1stTimeStamp+''''+' as datetime)'+@nl
    
    --print @sql
    delete from  @firstMatching 
    insert into @firstMatching  exec sp_executesql @sql    -- Get them all
    
    select top 1 cLsn as [matching LSN],convert(varchar,bTim,121) as [matching Timestamp] from @firstMatching;
    
    SET NOCOUNT OFF      -- Re-enable "rows affected display"
    
    end
    GO
    
  12. Create certificate on Master DB:

    Use [master]
    Go
    CREATE CERTIFICATE [attrep_rtm_position_1st_timestamp_cert]
    ENCRYPTION BY PASSWORD = N'choose_your_own_pwd'
    WITH SUBJECT = N'Certificate for FN_POSITION_1st_TIMESTAMP Permissions';
  13. Create login from the certificate as follows:

    Use [master]
    Go
    CREATE LOGIN attrep_rtm_position_1st_timestamp_login FROM CERTIFICATE
    [attrep_rtm_position_1st_timestamp_cert];
  14. Add the login to the sysadmin server role as follows:

    ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_position_1st_timestamp_login];
  15. Add the signature to [master].[attrep].[rtm_position_1st_timestamp] by the certificate as follows:

    Use [master]
    GO
    ADD SIGNATURE
    TO [master].[attrep].[rtm_position_1st_timestamp]
    BY CERTIFICATE [attrep_rtm_position_1st_timestamp_cert]
    WITH PASSWORD = 'choose_your_own_pwd';
    Note:

    If the stored procedure is recreated, you need to add the signature again.

  16. Create a user with the following permissions/roles (in each of the following databases):

    • Master DB:
      • select on sys.fn_dblog
      • view any definition
      • view server state (should be granted to the login).
      • execute on sp_repldone
      • execute on sp_replincrementlsn
      • execute on sp_addpublication
      • execute on sp_addarticle
      • execute on sp_articlefilter
      • select on [attrep].[split_partition_list]
      • execute on [attrep].[rtm_dump_dblog]
      • execute on [attrep].[rtm_position_1st_timestamp]
    • MSDB DB:
      • select on msdb.dbo.backupset
      • select on msdb.dbo.backupmediafamily
      • select on msdb.dbo.backupfile
    • For the source database:
      • db_owner ROLE