SQL server descobrir usuário que fez alteração

SQL server descobrir usuário que fez alteração

  28 de agosto de 2018    henrique

---

No post de hoje irei mostrar como descobrir o usuário e data/hora de alguma alteração no banco de dados SQL Server.
No caso irei selecionar do log que o SQL Server grava a cada operação feita, as operações de CREATE, DROP e ALTER, ou seja, as operações que alteram o schema do banco.
Para isso execute o script abaixo e se atente para os comentários que irão explicar o que está acontecendo nele e como configurar corretamente.
O script pode parecer complexo, mas é simples, em resumo iremos selecionar do arquivo de log do SQL Server os dados que ele mesmo grava, filtrar esses dados para o banco de dados a ser analisado e depois escolher as operações que citei acima.

use tempdb
go

declare @enable int

select top 1 @enable = convert(int,value_in_use) from sys.configurations 
where name = 'default trace enabled'

if @enable = 1

begin

declare @indx int ;
declare @curr_tracefilename varchar(500);
declare @base_tracefilename varchar(500);
declare @temp_trace table (obj_name nvarchar(256) collate database_default,
database_name nvarchar(256) collate database_default,start_time datetime,
event_class int,event_subclass int,object_type int,
server_name nvarchar(256) collate database_default,
login_name nvarchar(256) collate database_default,
application_name nvarchar(256) collate database_default,
ddl_operation nvarchar(40) collate database_default);

--Selecionando o arquivo de log na pasta do SQL Server
select @curr_tracefilename = path from sys.traces where is_default = 1 ; set @curr_tracefilename = reverse(@curr_tracefilename)

select @indx = PATINDEX('%\%', @curr_tracefilename); set @curr_tracefilename = reverse(@curr_tracefilename)

set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';

--Inserindo valores do log na tabela temporária
insert into @temp_trace select ObjectName,DatabaseName,StartTime,EventClass,EventSubClass,
ObjectType,ServerName,LoginName,ApplicationName,'temp'
from ::fn_trace_gettable( @base_tracefilename, default ) where EventClass in (46,47,164) 
and EventSubclass = 0 and DatabaseID <> 2 

-- selecionar o banco de dados a ser analisado
and DatabaseName = 'meu_banco_de_dados' 


-- Escolhendo as operações que desejo analisar
update @temp_trace set ddl_operation = 'CREATE' where event_class = 46
update @temp_trace set ddl_operation = 'DROP' where event_class = 47
update @temp_trace set ddl_operation = 'ALTER' where event_class = 164

select start_time as Event_Time,Database_name,Server_name,Login_name,Application_name,
DDL_Operation from @temp_trace where object_type not in (21587)
order by start_time desc

end