Vai al contenuto principale Passa a contenuto complementare

Autorizzazioni richieste

Per utilizzare Microsoft SQL Server (basato su registri) come origine in un'attività di trasferimento, l'utente specificato nelle proprietà di connessione Microsoft SQL Server (basato su registri) deve essere uno dei seguenti:

  • Un membro per il ruolo del database db_owner e il ruolo del server fisso sysAdmin.
  • Un membro per il ruolo del database db_owner, ma non un membro per il ruolo del server fisso sysAdmin. Per questo è necessario eseguire le procedure descritte nella sezione Impostazione di un utente non-sysadmin in un ambiente autonomo.

Impostazione di un utente non-sysadmin in un ambiente standalone

La seguente sezione descrive come configurare Microsoft SQL Server quando nelle impostazioni di connessione connettore è specificato un utente non amministratore.

Nota informatica

Se si desidera impostare un utente non amministratore in un ambiente AlwaysOn, vedere Impostazione di un utente non amministratore quando si lavora con i gruppi di disponibilità AlwaysOn.

Procedura di configurazione

Nota informatica

I passaggi da 16 a 20 devono essere eseguiti solo se le impostazioni del connettore sono configurate come segue:

  • Modalità di elaborazione delle modifiche (lettura delle modifiche da) è impostato su Privilegia i registri di backup o Solo registri di backup
  • La Cartella di backup alternativa è stata impostata

Per supportare un utente non amministratore di sistema:

  1. Configurare Microsoft SQL Server per la replica come descritto in Configurazione di Microsoft SQL Server per la replica.

  2. Abilitare MS-REPLICATION sul database di origine. Questa operazione può essere eseguita manualmente o eseguendo l'attività una volta con un utente sysadmin.

    Nota informatica

    Il distributore MS-REPLICATION deve essere configurato come impostazione locale o in modo da consentire l'accesso agli utenti non amministratori tramite il server associato.

  3. Creare lo schema [attrep] nel DB principale.
  4. Creare la funzione con valore di tabella [attrep].[split_partition_list] sul database 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. Creare la procedura [attrep].[rtm_dump_dblog] sul database Master:

    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
  6. Creare il certificato sul DB principale:

     

    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. Creare il login dal certificato:

     

    Use [master]
    Go
    CREATE LOGIN attrep_rtm_dump_dblog_login FROM CERTIFICATE 
    [attrep_rtm_dump_dblog_cert];
  8. Aggiungere il login al ruolo di server sysadmin:

    ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_dump_dblog_login];
  9. Aggiungere la firma del certificato a [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';
    Nota informatica

    Se la procedura archiviata viene ricreata, è necessario aggiungere nuovamente la firma.

  10. Creare la procedura [attrep].[rtm_position_1st_timestamp] sul database 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                 nvarchar(254) = '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. Creare il certificato sul DB principale:

     

    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. Creare il login dal certificato:

     

    Use [master]Go
    CREATE LOGIN attrep_rtm_position_1st_timestamp_login FROM CERTIFICATE
    [attrep_rtm_position_1st_timestamp_cert];
  13. Aggiungere il login al ruolo di server sysadmin:

     

    ALTER SERVER ROLE [sysadmin] ADD MEMBER [attrep_rtm_position_1st_timestamp_login];
  14. Aggiungere la firma a [master].[attrep].[rtm_position_1st_timestamp] da parte del certificato:

     

    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';
    Nota informatica

    Se la procedura archiviata viene ricreata, è necessario aggiungere nuovamente la firma.

  15. Creare la funzione [attrep].[rtm_check_file_exists] sul DB Master:

     

    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. Creare il certificato attrep_rtm_check_file_exists sul DB Master:

     

    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. Creare attrep_rtm_check_file_exists_login dal certificato:

     

    CREATE LOGIN attrep_rtm_check_file_exists_login

    FROM CERTIFICATE [attrep_rtm_check_file_exists_cert];

     

  18. Aggiungere il login al ruolo di server sysadmin:

     

    ALTER SERVER ROLE [sysadmin] ADD MEMBER attrep_rtm_check_file_exists_login;

     

  19. Aggiungere la firma alla funzione [rtm_check_file_exists] da parte del certificato:

     

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

    BY CERTIFICATE [attrep_rtm_check_file_exists_cert]

    WITH PASSWORD = N'choose_your_own_pwd';

    Nota informaticaSe la procedura archiviata viene ricreata, è necessario aggiungere nuovamente la firma.
  20. Utilizzare un utente di accesso esistente o creare un utente di accesso:

     

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

     

  21. Creare un utente nei database master e MSDB per l'utente di accesso:

     

    USE master

    GO

    CREATE USER [username] FOR LOGIN [username];

    GO

    USE msdb

    GO

    CREATE USER [username] FOR LOGIN [username];

    GO

     

    Ripetere questo passo per ogni database a cui si desidera accedere come utente non sysadmin.

  22. Assegnare all'utente le seguenti autorizzazioni e ruoli (in ciascuno dei seguenti database):

    • Master DB:
      • select on sys.fn_dblog
      • view any definition
      • view server state (dovrebbe essere concesso all'accesso).
      • 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]

        Nota informaticaQuesta autorizzazione è necessaria solo se si sono eseguiti i passaggi 16-20 di cui sopra.
    • MSDB DB:
      • select on msdb.dbo.backupset
      • select on msdb.dbo.backupmediafamily
      • select on msdb.dbo.backupfile
    • Per il database di origine:
      • db_owner ROLE
  23. Fornire l'accesso a un ruolo non sysadmin, in modo che l'endpoint sia in grado di creare una pubblicazione:

    USE master

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

    EXEC <databaseName>.sys.sp_addlogreader_agent

Hai trovato utile questa pagina?

Se riscontri problemi con questa pagina o con il suo contenuto – un errore di battitura, un passaggio mancante o un errore tecnico – facci sapere come possiamo migliorare!