none
Listar acesso de usuários em base de dados de uma instancia do SQL SERVER RRS feed

  • Pergunta

  • Bom dia a todos,

    Pessoal, preciso de uma ajuda, preciso listar os acessos e execução realizados dos usuários do SQL SERVER em uma instancia.

    Preciso realizar este procedimento via linha de texto porque vou usar em uma ETL.

    Alguém saberia como resolver este caso?

    Obrigado

    sexta-feira, 7 de junho de 2019 12:59

Todas as Respostas

  • Roberto,

    Este primeiro exemplo, poderá lhe ajudar a monitorar e identificar os acessos realizados ao seu servidor ou instância SQL Server:

    USE master;
    CREATE TABLE Ultimo_Login (
       [LoginName] sysname primary key,
       Data_conexao datetime,
       Evento xml
    );
    
    USE master
    Go
    
    CREATE TRIGGER [Monitora_Login] 
       on ALL SERVER  with execute as '____'
       after LOGON    
       as
    begin
    declare @Evento XML, @LoginName sysname, @Data_conexao datetime;
    set @Evento= Eventdata();
    set @LoginName= @Evento.value ('(/EVENT_INSTANCE/LoginName)[1]','sysname');
    set @Data_conexao= @Evento.value ('(/EVENT_INSTANCE/PostTime)[1]','datetime');
    
    IF exists (SELECT * from master..Ultimo_Login where [LoginName] = @LoginName)
      UPDATE master..Ultimo_Login
        set Data_conexao= @Data_conexao,
            Evento= @Evento
        where [LoginName] = @LoginName
    else
      INSERT into master..Ultimo_Login ([LoginName], Data_conexao, Evento)
          values (@LoginName, @Data_conexao, @Evento);
    
    end;
    go
    
    declare @3meses datetime;
    set @3meses= dateadd (month, -3, cast(current_timestamp as date));
    
    SELECT P.name, P.type_desc, P.create_date, UL.Data_conexao
      from sys.server_principals as P
           left join master..Ultimo_Login as UL on UL.[LoginName] = P.name
      where P.is_disabled <> 1
            and P.type in ('S', 'U')
            and P.create_date < @3meses
            and (UL.[LoginName] is null or UL.Data_conexao < @3meses)
      order by P.name;

    Este outro exemplo, vai lhe permitir identificar os últimos acessos, obtendo as operações de leitura e escrita realizada pelos usuários:

    ;WITH myCTE AS
    (
    SELECT
      DB_NAME(database_id) AS TheDatabase,
      last_user_seek,
      last_user_scan,
      last_user_lookup,
      last_user_update
    FROM sys.dm_db_index_usage_stats
    )
    SELECT
      ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name='tempdb'),
      x.TheDatabase,
      MAX(x.last_read) AS  last_read,
      MAX(x.last_write) AS last_write
    FROM
    (
    SELECT TheDatabase,last_user_seek AS last_read, NULL AS last_write FROM myCTE
      UNION ALL
    SELECT TheDatabase,last_user_scan, NULL FROM myCTE
      UNION ALL
    SELECT TheDatabase,last_user_lookup, NULL FROM myCTE
      UNION ALL
    SELECT TheDatabase,NULL, last_user_update FROM myCTE
    ) AS x
    
    GROUP BY TheDatabase
    ORDER BY TheDatabase

    Já este outro exemplo, Podemos implementar uma auditoria em tempo real:

    SELECT ser.session_id As 'SessionID',
    	         ssp.ecid,
    	         DB_NAME(ssp.dbid) As 'DatabaseName',
    	         ssp.nt_username as 'User',
    	         ser.status As 'Status',
    	         ser.wait_type As 'Wait',
    		     SUBSTRING (sqt.text,  ser.statement_start_offset/2,
    	           (CASE WHEN 
    			              ser.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), sqt.text)) * 2
    		         ELSE ser.statement_end_offset 
    				 END - ser.statement_start_offset)/2) As 'Individual Query',
    	         sqt.text As 'Parent Query',
    	         ssp.program_name As 'ProgramName',
    	         ssp.hostname,
    	         ssp.nt_domain As 'NetworkDomain',
    	         ser.start_time
    FROM sys.dm_exec_requests ser INNER JOIN sys.sysprocesses ssp 
                                                           On ser.session_id = ssp.spid
    CROSS APPLY sys.dm_exec_sql_text(ser.sql_handle)as sqt
    WHERE ser.session_Id > 50             
    AND ser.session_Id NOT IN (@@SPID)     
    ORDER BY SessionID, ssp.ecid
    Go
    

    Identificando as permissões de usuário:

    -- Criando a tabela para armazenar o histórico de acessos --
    CREATE TABLE [dbo].[Auditoria_Acesso]
    (
        [Id_Auditoria] [bigint] NOT NULL IDENTITY(1, 1),
        [Dt_Auditoria] [datetime] NOT NULL,
        [Cd_Acao] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Ds_Maquina] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Ds_Usuario] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Ds_Database] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Ds_Schema] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Ds_Objeto] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Ds_Query] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [Fl_Sucesso] [bit] NOT NULL,
        [Ds_IP] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Ds_Programa] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Qt_Duracao] [bigint] NOT NULL,
        [Qt_Linhas_Retornadas] [bigint] NOT NULL,
        [Qt_Linhas_Alteradas] [bigint] NOT NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    WITH
    (
    DATA_COMPRESSION = PAGE
    )
    GO
    
    ALTER TABLE [dbo].[Auditoria_Acesso] ADD CONSTRAINT [PK__Auditori__E9F1DAD4EE3743FE] PRIMARY KEY CLUSTERED ([Id_Auditoria]) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY]
    GO
    
    -- Criando a Server Audit filtrando os usuários --
    USE [master]
    GO
    
    IF ((SELECT COUNT(*) FROM sys.server_audits WHERE [name] = 'Auditoria_Acessos') > 0)
    BEGIN
        ALTER SERVER AUDIT [Auditoria_Acessos] WITH (STATE = OFF);
        DROP SERVER AUDIT [Auditoria_Acessos]
    END
    
    
    CREATE SERVER AUDIT [Auditoria_Acessos]
    TO FILE
    (	
        FILEPATH = N'C:\Audit\',
        MAXSIZE = 10 MB,
        MAX_ROLLOVER_FILES = 16,
        RESERVE_DISK_SPACE = OFF
    )
    WITH
    (	
        QUEUE_DELAY = 1000,
        ON_FAILURE = CONTINUE,
        AUDIT_GUID = '0b5ad307-ee47-43db-a169-9af67cb661f9'
    )
    WHERE (([server_principal_name] LIKE '%User' OR [server_principal_name] LIKE 'LS_%') AND [application_name]<>'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense' AND NOT [application_name] LIKE 'Red Gate Software%' AND NOT [server_principal_name] LIKE 'GRUPODADALTO\%' AND NOT [server_principal_name] LIKE 'WWWUser')
    GO
    
    
    ALTER SERVER AUDIT [Auditoria_Acessos] WITH (STATE = ON)
    GO
    
    
    -- Criando a Database Audit capturando os acessos --
    DECLARE @Query VARCHAR(MAX)
    SET @Query = '
    
    IF (''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb''))
    BEGIN
    
        USE [?];
    
        IF ((SELECT COUNT(*) FROM sys.database_audit_specifications WHERE [name] = ''Auditoria_Acessos'') > 0)
        BEGIN
    
            ALTER DATABASE AUDIT SPECIFICATION [Auditoria_Acessos] WITH (STATE = OFF);
            DROP DATABASE AUDIT SPECIFICATION [Auditoria_Acessos];
    
        END
    
        CREATE DATABASE AUDIT SPECIFICATION [Auditoria_Acessos]
        FOR SERVER AUDIT [Auditoria_Acessos]
        ADD (DELETE ON DATABASE::[?] BY [public]),
        ADD (EXECUTE ON DATABASE::[?] BY [public]),
        ADD (INSERT ON DATABASE::[?] BY [public]),
        ADD (SELECT ON DATABASE::[?] BY [public]),
        ADD (UPDATE ON DATABASE::[?] BY [public])
        WITH (STATE = ON);
        
    END'
    Go
    
    EXEC sys.sp_MSforeachdb @Query
    Go
    
    -- Criando a Stored Procedure para armazenar os dados coletados --
    IF (OBJECT_ID('dbo.stpAuditoria_Acessos_Carrega_Dados') IS NULL) EXEC('CREATE PROCEDURE dbo.stpAuditoria_Acessos_Carrega_Dados AS SELECT 1')
    GO
    
    ALTER PROCEDURE dbo.stpAuditoria_Acessos_Carrega_Dados
    AS
    BEGIN
    
        DECLARE @TimeZone INT = DATEDIFF(HOUR, GETUTCDATE(), GETDATE())
        DECLARE @Dt_Max DATETIME = DATEADD(SECOND, 1, ISNULL((SELECT MAX(Dt_Auditoria) FROM .Auditoria_Acesso), '1900-01-01'))
    
        INSERT INTO dbo.Auditoria_Acesso
        (
            Dt_Auditoria,
            Cd_Acao,
            Ds_Maquina,
            Ds_Usuario,
            Ds_Database,
            Ds_Schema,
            Ds_Objeto,
            Ds_Query
            Fl_Sucesso,
            Ds_IP,
            Ds_Programa,
            Qt_Duracao,
            Qt_Linhas_Retornadas,
            Qt_Linhas_Alteradas
        )
        SELECT DISTINCT
            DATEADD(HOUR, @TimeZone, event_time) AS event_time,
            action_id,
            server_instance_name,
            server_principal_name,
            [database_name],
            [schema_name],
            [object_name],
            [statement],
            succeeded,
            client_ip,
            application_name,
            duration_milliseconds,
            response_rows,
            affected_rows
        FROM 
            sys.fn_get_audit_file('C:\Audit\*.sqlaudit', DEFAULT, DEFAULT)
        WHERE 
            DATEADD(HOUR, @TimeZone, event_time) >= @Dt_Max
    
    END
    Go
    
    -- Consultando os dados coletados --
    Select * from Auditoria_Acessos
    Go
    
    -- Mapeando as permissões com base na auditoria --
    SELECT DISTINCT 
        Ds_Usuario,
        Ds_Database, 
        Cd_Acao, 
        Ds_Objeto,
        'USE [' + Ds_Database + ']; GRANT ' + (CASE Cd_Acao
            WHEN 'UP' THEN 'UPDATE'
            WHEN 'IN' THEN 'INSERT'
            WHEN 'DL' THEN 'DELETE'
            WHEN 'SL' THEN 'SELECT'
            WHEN 'EX' THEN 'EXECUTE'
        END) + ' ON [' + Ds_Schema + '].[' + Ds_Objeto + '] TO [' + Ds_Usuario + '];' AS Comando 
    FROM 
        dirceuresende..Auditoria_Acesso 
    WHERE 
        Cd_Acao <> 'UNDO'
    ORDER BY
        Ds_Usuario,
        Ds_Database,
        Ds_Objeto
    Go

    Neste outro exemplo, você vai poder encontrar os commandos processados:

    create table TABELA_AUDITORIA
    (Servidor varchar(200),
     NomeHost varchar(200),
     Usuario varchar(200),
     [Login] varchar(200),
     Aplicacao varchar(200),
     Horario datetime,
     Comando varchar(200))
    
    Alter TRIGGER AUDITORIA
    ON ALL SERVER
    FOR
    CREATE_DATABASE, DROP_DATABASE
    AS
    
    BEGIN
        DECLARE @Comando varchar(100), @Query varchar(max) 
        CREATE TABLE #inputbuffer 
        ( 
         EventType varchar(100), 
         Parameters int, 
         EventInfo varchar(max) 
        ) 
        SET @Comando = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')' 
        INSERT INTO #inputbuffer 
        EXEC (@Comando) 
        SET @Query = (SELECT EventInfo FROM #inputbuffer) 
      
        DECLARE @Horario varchar(100) 
        DECLARE @NomeHost varchar(100) 
        DECLARE @Servidor varchar(50)
        DECLARE @NomeUsuario varchar(100) 
        DECLARE @Login varchar(100) 
        DECLARE @Aplicacao nvarchar(300) 
      
        SET @Horario = CURRENT_TIMESTAMP 
        SET @NomeHost = HOST_NAME() 
        SET @Servidor = @@servername
        SET @NomeUsuario = USER 
        SET @Login = SYSTEM_USER 
        SET @Aplicacao = PROGRAM_NAME() 
    
    
    	INSERT INTO Master..TABELA_AUDITORIA(Servidor, NomeHost, Usuario, Login, Aplicacao, Horario, Comando) 
    	VALUES(@Servidor, @NomeHost, @NomeUsuario, @Login, @Aplicacao,@Horario, @Query) 
    	
    END


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Sugerido como Resposta IgorFKModerator segunda-feira, 10 de junho de 2019 11:59
    sexta-feira, 7 de junho de 2019 18:24