メイン コンテンツをスキップする 補完的コンテンツへスキップ

必要な権限

Microsoft SQL Server (ログベース) を タスクのソースとして使用するには、Microsoft SQL Server (ログベース) の [接続プロパティ] で指定したユーザーが次の条件を満たしている必要があります。

  • db_owner データベース役割と sysAdmin 固定サーバー役割の両方のメンバーである。
  • db_owner データベース役割のメンバーだが、sysAdmin 固定サーバー役割のメンバーではない。この場合も、下記の「スタンドアロン環境での非シスアドユーザーの設定」に記載されている手順を実行する必要があります。

スタンドアロン環境における non-sysadmin のユーザーの設定

次のセクションでは、non-sysadmin ユーザーがコネクタ接続設定で指定されている場合に、Microsoft SQL Server を設定する方法について説明しています。

情報メモ

AlwaysOn 環境で non-sysadmin ユーザーを設定する場合、「AlwaysOn 可用性グループを処理する際、non-sysadmin ユーザーを設定する」を参照してください。

セットアップ手順

情報メモ

ステップ 16~20 は、コネクタ設定が次のような場合にのみ実行する必要があります。

  • [処理モードの変更 (変更読み取り元)] が [バックアップ ログを優先] または [バックアップ ログのみ] に設定されている
  • [代替バックアップ フォルダー] が設定されている

non-sysadmin ユーザーをサポートするには:

  1. 複製のための Microsoft SQL Server の設定 の説明に従って、Microsoft SQL Server for Replication を設定します。

  2. ソース データベースで MS-REPLICATION を有効化します。これは、手動で、または sysadmin ユーザーを使ってタスクを一回実行することにより可能です。

    情報メモ

    MS-REPLICATION ディストリビューターは、ローカルとして、または関連付けられたリンク済みサーバー経由で non-sysadmin ユーザーにアクセスできる方法で構成できます。

  3. マスター DB にスキーマ [attrep] を作成します。
  4. マスター データベースにテーブル値関数 [attrep].[split_partition_list] を作成します。

     

    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
  5. マスター データベースにプロシージャ [attrep].[rtm_dump_dblog] を作成します。

    
    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
    		 [Xact Id] -- Only needed if you are using the AR_H_XACT_ID custom header
            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 
    		 [Xact Id] -- Only needed if you are using the AR_H_XACT_ID custom header
    	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
  6. マスター 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';
  7. 証明書からログインを作成します。

     

    Use [master]
    Go
    CREATE LOGIN attrep_rtm_dump_dblog_login FROM CERTIFICATE 
    [attrep_rtm_dump_dblog_cert];
  8. sysadmin サーバーにログインを追加します。

    ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_dump_dblog_login];
  9. 証明書署名を [master].[attrep].[rtm_dump_dblog]:

     

    Use [master]
    GO
    ADD SIGNATURE
    TO [master].[attrep].[rtm_dump_dblog]
    BY CERTIFICATE [attrep_rtm_dump_dblog_cert]
    WITH PASSWORD = 'choose_your_own_pwd';
    情報メモ

    保管された手順が再作成された場合、署名をもう一度追加する必要があります。

  10. マスター データベースにプロシージャ [attrep].[rtm_position_1st_timestamp] を作成します。

     

    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
    
  11. マスター 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';
  12. 証明書からログインを作成します。

     

    Use [master]
    Go
    CREATE LOGIN attrep_rtm_position_1st_timestamp_login FROM CERTIFICATE
    [attrep_rtm_position_1st_timestamp_cert];
  13. sysadmin サーバーにログインを追加します。

     

    ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_position_1st_timestamp_login];
  14. 証明書の署名を [master].[attrep].[rtm_position_1st_timestamp] に追加します。

     

    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';
    情報メモ

    保管された手順が再作成された場合、署名をもう一度追加する必要があります。

  15. マスター DB で関数 [attrep].[rtm_check_file_exists] を作成します。

     

    USE [master]

    GO

     

    IF object_id('[attrep].[rtm_check_file_exists]','FN') IS NOT NULL

    DROP FUNCTION [attrep].[rtm_check_file_exists];

    GO

     

    CREATE FUNCTION [attrep].[rtm_check_file_exists]

    (

    @filename varchar(260)

    ) returns int

    as

    begin

    declare @exists int = 0;

    exec master.dbo.xp_fileexist @filename, @exists OUT;

    return @exists;

    end

    GO

  16. マスター DB で attrep_rtm_check_file_exists 証明書を作成します。

     

    CREATE CERTIFICATE [attrep_rtm_check_file_exists_cert]

    ENCRYPTION BY PASSWORD = N'choose_your_own_pwd'

    WITH SUBJECT = N'Certificate for rtm_check_file_exists Permissions';

     

  17. 証明書から attrep_rtm_check_file_exists_login を作成します。

     

    CREATE LOGIN attrep_rtm_check_file_exists_login

    FROM CERTIFICATE [attrep_rtm_check_file_exists_cert];

     

  18. sysadmin サーバーにログインを追加します。

     

    ALTER SERVER ROLE [sysadmin] ADD MEMBER attrep_rtm_check_file_exists_login;

     

  19. 証明書の署名を関数 [rtm_check_file_exists] に追加します。

     

    ADD SIGNATURE TO [master].[attrep].[rtm_check_file_exists]

    BY CERTIFICATE [attrep_rtm_check_file_exists_cert]

    WITH PASSWORD = N'choose_your_own_pwd';

    情報メモ保管された手順が再作成された場合、署名をもう一度追加する必要があります。
  20. 既存のログイン ユーザーを使用するか、ログイン ユーザーを作成します。

     

    CREATE LOGIN [username] WITH PASSWORD='password';

     

  21. マスター データベースと MSDB データベースで、ログイン ユーザーのユーザーを作成します。

     

    USE master

    GO

    CREATE USER [username] FOR LOGIN [username];

    GO

    USE msdb

    GO

    CREATE USER [username] FOR LOGIN [username];

    GO

     

    sysadmin 以外のユーザーでアクセスするデータベースごとに、このステップを繰り返します。

  22. ユーザーに次の権限と役割を付与します (次のデータベースのそれぞれで)。

    • マスター DB:
      • sys.fn_dblog で選択する
      • 定義を表示する
      • サーバー状態を表示する (ログインに付与される)。
      • sp_repldone を実行する
      • sp_replincrementlsn を実行する
      • sp_addpublication を実行する
      • sp_addarticle を実行する
      • sp_articlefilter を実行する
      • [attrep].[split_partition_list] で選択する
      • [attrep].[rtm_dump_dblog] で実行する
      • [attrep].[rtm_position_1st_timestamp] で実行する
      • [attrep].[rtm_check_file_exists] で実行する

        情報メモこの権限は、上記のステップ 16~20 を実行した場合にのみ必須となります。
    • MSDB DB:
      • msdb.dbo.backupset で選択する
      • msdb.dbo.backupmediafamily で選択する
      • msdb.dbo.backupfile で選択する
    • ソース データベースの場合:
      • db_owner 役割
  23. Qlik Talend Data Integration が sysadmin 権限なしで公開を作成できるようにするには、公開フラグを設定し、ログ リーダー ジョブを事前に作成します。

    USE master

    EXEC sp_replicationdboption @dbname = '<databaseName>', @optname = 'publish', @value = 'true'

    EXEC <databaseName>.sys.sp_addlogreader_agent

このページは役に立ちましたか?

このページまたはコンテンツに、タイポ、ステップの省略、技術的エラーなどの問題が見つかった場合は、お知らせください。改善に役立たせていただきます。