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

Question

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

Answer 1

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]