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

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