Accéder au contenu principal Passer au contenu complémentaire
Ressources Qlik

Autorisations requises

Pour utiliser Microsoft SQL Server (Log-Based) en tant que source dans une tâche de dépôt temporaire, l'utilisateur spécifié dans les propriétés de connexion Microsoft SQL Server (Log-Based) doit être l'un des utilisateurs suivants :

  • Un membre du rôle de base de données db_owner et du rôle de serveur fixe sysAdmin.

  • Un membre du rôle de base de données db_owner, mais pas un membre du rôle de serveur fixe sysAdmin. Cela vous oblige également à effectuer les procédures décrites à la section Configuration d'un utilisateur non sysadmin dans un environnement autonome ci-dessous.

Configuration d'un utilisateur non sysadmin dans un environnement autonome

La section suivante explique comment configurer Microsoft SQL Server lorsqu'un utilisateur non sysadmin est spécifié dans les paramètres de connexion connecteur.

Note Informations

Si vous souhaitez configurer un utilisateur non sys-admin dans un environnement AlwaysOn, voir Configuration d'un utilisateur non-sysadmin lors de l'utilisation des groupes de disponibilité AlwaysOn.

Procédure de configuration

Note Informations

Les étapes 16 à 20 doivent être effectuées uniquement si les paramètres de connecteur sont comme suit :

  • Mode de traitement des modifications (Lires les modifications depuis) est défini sur Prioriser les journaux de sauvegarde ou Journaux de sauvegarde uniquement.
  • Autre dossier de sauvegarde est activé.

Pour prendre en charge un utilisateur non-sysadmin :

  1. Configurez Microsoft SQL Server pour Replication comme indiqué dans Configuration de Microsoft SQL Server pour la réplication.

  2. Activez MS-REPLICATION sur la base de données source. Cela peut être fait manuellement ou en exécutant la tâche une fois en utilisant un utilisateur sysadmin.

    Note Informations

    Le distributeur MS-REPLICATION doit être configuré soit comme local, soit de manière à permettre l'accès aux utilisateurs non sysadmin via le serveur lié associé.

  3. Créez le schéma [attrep] dans la base de données principale Master DB.

  4. Créez la fonction valorisée par la table [attrep].[split_partition_list] sur la base de données principale Master :

     

    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. Créez la procédure [attrep].[rtm_dump_dblog] sur la base de données principale Master : 

    
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. Créez un certificat sur la base de données principale 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';

  7. Créez un identifiant à partir du certificat :

     

    Use [master]
Go
CREATE LOGIN attrep_rtm_dump_dblog_login FROM CERTIFICATE 
[attrep_rtm_dump_dblog_cert];

  8. Ajoutez l'identifiant au rôle de serveur sysadmin :

    ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_dump_dblog_login];

  9. Ajoutez la signature du certificat à [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';
    Note Informations

    Si la procédure stockée est recréée, vous devez à nouveau ajouter la signature.

  10. Créez la procédure [attrep].[rtm_position_1st_timestamp] sur la base de données principale Master :

     

    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. Créez un certificat sur la base de données principale 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';

  12. Créez un identifiant à partir du certificat :

     

    Use [master]
Go
CREATE LOGIN attrep_rtm_position_1st_timestamp_login FROM CERTIFICATE
[attrep_rtm_position_1st_timestamp_cert];

  13. Ajoutez l'identifiant au rôle de serveur sysadmin :

     

    ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_position_1st_timestamp_login];

  14. Ajoutez la signature à [master].[attrep].[rtm_position_1st_timestamp] par le certificat :

     

    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 Informations

    Si la procédure stockée est recréée, vous devez à nouveau ajouter la signature.

  15. Créez la fonction [attrep].[rtm_check_file_exists] sur la base de données principale Master DB :

     

    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. Créez le certificat attrep_rtm_check_file_exists sur la base de données principale Master DB :

     

    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. Créez attrep_rtm_check_file_exists_login à partir du certificat :

     

    CREATE LOGIN attrep_rtm_check_file_exists_login

    FROM CERTIFICATE [attrep_rtm_check_file_exists_cert];

     

  18. Ajoutez l'identifiant au rôle de serveur sysadmin :

     

    ALTER SERVER ROLE [sysadmin] ADD MEMBER attrep_rtm_check_file_exists_login;

     

  19. Ajoutez la signature à la fonction [rtm_check_file_exists] par le certificat :

     

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

    BY CERTIFICATE [attrep_rtm_check_file_exists_cert]

    WITH PASSWORD = N'choose_your_own_pwd';

    Note InformationsSi la procédure stockée est recréée, vous devez à nouveau ajouter la signature.

  20. Utilisez un utilisateur de connexion existant ou créez un utilisateur de connexion :

     

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

     

  21. Créez un utilisateur dans les bases de données principale et MSDB pour l'utilisateur de connexion :

     

    USE master

    GO

    CREATE USER [username] FOR LOGIN [username];

    GO

    USE msdb

    GO

    CREATE USER [username] FOR LOGIN [username];

    GO

     

    Répétez cette étape pour chaque base de données à laquelle vous souhaitez accéder avec un utilisateur autre que sysadmin.

  22. Accordez à l'utilisateur les autorisations et rôles suivants (dans chacune des bases de données suivantes) :

    • Base de données principale Master DB :
      • select on sys.fn_dblog
      • view any definition
      • view server state (accordé à l'identifiant).
      • 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]

      • execute on [attrep].[rtm_check_file_exists]

        Note InformationsCette autorisation est requise uniquement si vous effectuez les étapes 16 à 20 ci-dessus.
    • BD MSDB :
      • select on msdb.dbo.backupset
      • select on msdb.dbo.backupmediafamily
      • select on msdb.dbo.backupfile
    • Pour la base de données source :
      • db_owner ROLE

  23. Accordez un accès au rôle non-sysadmin de sorte que le point de terminaison soit capable de créer une publication :

    USE master

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

    EXEC <databaseName>.sys.sp_addlogreader_agent

Cette page vous a-t-elle aidé ?

Si vous rencontrez des problèmes sur cette page ou dans son contenu – une faute de frappe, une étape manquante ou une erreur technique – dites-nous comment nous améliorer !

Laissez vos commentaires ici