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.

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

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:

Configurare Microsoft SQL Server per la replica come descritto in Configurazione di Microsoft SQL Server per la replica.
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.
Creare lo schema [attrep] nel DB principale.

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

Creare la procedura [attrep].[rtm_dump_dblog] sul database 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

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

Creare il login dal certificato:

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

Aggiungere il login al ruolo di server sysadmin:

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

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.

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

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

Creare il login dal certificato:

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

Aggiungere il login al ruolo di server sysadmin:

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

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.
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
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';
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];
Aggiungere il login al ruolo di server sysadmin:

ALTER SERVER ROLE [sysadmin] ADD MEMBER attrep_rtm_check_file_exists_login;
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.
Utilizzare un utente di accesso esistente o creare un utente di accesso:
```
CREATE LOGIN [username] WITH PASSWORD='password';
```
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.
Assegnare all'utente le seguenti autorizzazioni e ruoli (in ciascuno dei seguenti database):
- Master DB:
  - può effettuare selezioni su sys.fn_dblog
  - può visualizzare qualsiasi definizione
  - può visualizzare lo stato del server (deve essere concesso all'accesso).
  - può eseguire operazioni su sp_repldone
  - può eseguire operazioni su sp_replincrementlsn
  - può eseguire operazioni su sp_addpublication
  - può eseguire operazioni su sp_addarticle
  - può eseguire operazioni su sp_articlefilter
  - può effettuare selezioni su [attrep].[split_partition_list]
  - può eseguire operazioni su [attrep].[rtm_dump_dblog]
  - può eseguire operazioni su [attrep].[rtm_position_1st_timestamp]
  - può eseguire operazioni su [attrep].[rtm_check_file_exists]
    Nota informaticaQuesta autorizzazione è necessaria solo se si sono eseguiti i passaggi 16-20 di cui sopra.
- MSDB DB:
  - può effettuare selezioni su msdb.dbo.backupset
  - può effettuare selezioni su msdb.dbo.backupmediafamily
  - può effettuare selezioni su msdb.dbo.backupfile
- Per il database di origine:
  - db_owner ROLE
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!

Lascia qui il tuo feedback