Como descobrir quem excluiu alguns dados SQL Server

Eu não tentei fn_dblog no Express, mas se estiver disponível, o seguinte fornecerá operações de exclusão:

SELECT 
    * 
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    Operation = 'LOP_DELETE_ROWS'

Pegue o ID da transação para transações em que você está interessado e identifique o SID que iniciou a transação com:

SELECT
    [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = @TranID
AND
    [Operation] = 'LOP_BEGIN_XACT'

Em seguida, identifique o usuário do SID:

SELECT
    *
FROM 
    sysusers
WHERE
    [sid] = @SID

Edit: Juntando tudo isso para encontrar exclusões em uma tabela especificada:

DECLARE @TableName sysname
SET @TableName = 'dbo.Table_1'

SELECT
    u.[name] AS UserName
    , l.[Begin Time] AS TransactionStartTime
FROM
    fn_dblog(NULL, NULL) l
INNER JOIN
    (
    SELECT
        [Transaction ID]
    FROM 
        fn_dblog(NULL, NULL) 
    WHERE
        AllocUnitName LIKE @TableName + '%'
    AND
        Operation = 'LOP_DELETE_ROWS'
    ) deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
INNER JOIN
    sysusers u
ON  u.[sid] = l.[Transaction SID]

Se o banco de dados estiver no modo de recuperação total ou se você tiver backups de log de transações, tente lê-los usando leitores de log de terceiros.

Você pode experimentar o ApexSQL Log (premium, mas tem uma avaliação gratuita) ou SQL Log Rescue (gratuito, mas apenas sql 2000).

como eles poderiam descobrir quem excluiu alguns dados em seu banco de dados SQL Server

Embora isso seja respondido, queria acrescentar que o SQL Server tem um rastreamento padrão habilitado e pode ser usado para descobrir quem derrubou/alterou os objetos.

Eventos de objetos

Os eventos de objeto incluem: Objeto Alterado, Objeto Criado e Objeto Excluído

observação: o SQL Server por padrão tem 5 arquivos de rastreamento, 20 MB cada e não há nenhum método conhecido com suporte para alterar isso. Se você tiver um sistema ocupado, os arquivos de rastreamento podem rolar muito rápido (mesmo dentro de horas) e você pode não conseguir capturar algumas das alterações.

Excelente exemplo pode ser encontrado: O rastreamento padrão no SQL Server - o poder da auditoria de desempenho e segurança

Você pode tentar este procedimento para consultar os arquivos de backup de log e descobrir em qual(is) arquivo(s) de backup de log um valor específico de uma coluna de uma tabela ainda estava presente/último.

Para localizar o usuário, após localizar em qual backup de log o valor existia pela última vez, você pode restaurar um banco de dados até esse backup de log e, em seguida, seguir a resposta de Mark Storey-Smith .

Alguns pré-requisitos

• saber quais valores de quais colunas foram excluídas
• Estão sob o modelo de recuperação completa e estão fazendo backups de log
• você tem datas ou identificadores em seus backups de log, como ao usar a solução de Ola Hallengren

Isenção de responsabilidade

Esta solução está longe de ser à prova d'água, e muito mais trabalho precisa ser feito.

Ele não foi testado em ambientes de grande escala, ou mesmo em qualquer ambiente além de alguns pequenos testes. A execução atual foi no SQL Server 2017.

Você pode usar o procedimento abaixo de Muhammad Imran que modifiquei para trabalhar com o conteúdo dos backups de log em vez do conteúdo do log de um banco de dados ativo.

Dessa forma, você tecnicamente não está fazendo restaurações, mas despejando o conteúdo do log em uma tabela temporária. Provavelmente ainda será lento e está muito aberto a bugs e problemas. Mas poderia funcionar, em teoria™.

O procedimento armazenado usa a fn_dump_dblogfunção não documentada para ler os arquivos de log.

Ambiente de teste

Considere este banco de dados, onde inserimos algumas linhas, fazemos 2 backups de log e, no terceiro backup de log, excluímos todas as linhas.

CREATE DATABASE WrongDeletesDatabase
GO
USE WrongDeletesDatabase
GO
BACKUP DATABASE WrongDeletesDatabase TO DISK ='c:\temp\Full.bak'

ALTER DATABASE WrongDeletesDatabase SET RECOVERY FULL
GO

CREATE TABLE dbo.WrongDeletes(ID INT, val varchar(255))

INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (1,'value1')
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log1.trn'
GO
INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (2,'value2')
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log2.trn'
GO
DELETE FROM dbo.WrongDeletes
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log3.trn'
GO
INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (3,'value3')
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log4.trn'
GO

O procedimento

Você pode encontrar e baixar o procedimento armazenado aqui .

Eu não poderia adicioná-lo aqui, pois é maior que o limite de caracteres e tornaria essa resposta ainda menos clara do que é.

Além disso, você deve ser capaz de executar o procedimento.

Executando o procedimento

Um exemplo disso, quando adiciono todos os meus arquivos de log ( 4) ao procedimento armazenado e executo o procedimento procurando por valor1

EXEC dbo.Recover_Deleted_Data_Proc  @Database_Name= 'WrongDeletesDatabase',
                                    @SchemaName_n_TableName= 'dbo.WrongDeletes', 
                                    @SearchString = 'value1', 
                                    @SearchColumn = 'val',
                                    @LogBackupFolder ='C:\temp\Logs\'

Isso me pega:

ID  val LogFileName
1   value1  c:\temp\Logs\log3.trn
1   value1  c:\temp\Logs\log1.trn

Onde podemos encontrar quando foi a última vez que uma operação value1aconteceu, a exclusão em log3.trn.

Mais alguns dados de teste, adicionando uma tabela com colunas diferentes

CREATE TABLE dbo.WrongDeletes2(Wow varchar(255), Anotherval varchar(255),Val3 int)

INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (1,'value1')
INSERT INTO dbo.WrongDeletes2(wOw,Anotherval,Val3)
VALUES ('b','value1',1)
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log1_1.trn'
GO
INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (2,'value2')
INSERT INTO dbo.WrongDeletes2(wOw,Anotherval,Val3)
VALUES ('c','value2',2)
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log2_1.trn'
GO
DELETE FROM dbo.WrongDeletes
DELETE FROM dbo.WrongDeletes2
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log3_1.trn'
GO
INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (3,'value3')
INSERT INTO dbo.WrongDeletes2(wOw,Anotherval,Val3)
VALUES ('d','value3',3)
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log4_1.trn'
GO

Alterando os nomes dos arquivos de log e executando o procedimento novamente

EXEC dbo.Recover_Deleted_Data_Proc  @Database_Name= 'WrongDeletesDatabase',
                                    @SchemaName_n_TableName= 'dbo.WrongDeletes', 
                                    @SearchString = 'value1', 
                                    @SearchColumn = 'val',
                                    @LogBackupFolder ='C:\temp\Logs\'

Resultado

ID  val LogFileName
1   value1  c:\temp\Logs\log1_1.trn
1   value1  c:\temp\Logs\log3_1.trn
1   value1  c:\temp\Logs\log3_1.trn

Uma nova execução, procurando o inteiro ( 2) na val3coluna dedbo.WrongDeletes2

EXEC dbo.Recover_Deleted_Data_Proc  @Database_Name= 'WrongDeletesDatabase',
                                    @SchemaName_n_TableName= 'dbo.WrongDeletes2', 
                                    @SearchString = '2', 
                                    @SearchColumn = 'Val3',
                                    @LogBackupFolder ='C:\temp\Logs\'

Resultado

Anotherval  Val3    Wow LogFileName
value2  2   c   c:\temp\Logs\log2.trn
value2  2   c   c:\temp\Logs\log3.trn

Aplicando a resposta de Mark Storey-Smith

Sabemos agora que aconteceu no terceiro arquivo de log, vamos restaurar até esse ponto:

USE master
GO
ALTER DATABASE WrongDeletesDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE WrongDeletesDatabase SET ONLINE 
GO
RESTORE DATABASE WrongDeletesDatabase FROM DISK = 'c:\temp\Logs\Full.bak' WITH NORECOVERY,REPLACE
RESTORE LOG WrongDeletesDatabase FROM DISK = 'c:\temp\Logs\log1.trn' WITH NORECOVERY
RESTORE LOG WrongDeletesDatabase FROM DISK = 'c:\temp\Logs\log2.trn' WITH NORECOVERY
RESTORE LOG WrongDeletesDatabase FROM DISK = 'c:\temp\Logs\log3.trn' WITH RECOVERY
GO
USE WrongDeletesDatabase
GO

Executando a última consulta em sua resposta

SELECT
    u.[name] AS UserName
    , l.[Begin Time] AS TransactionStartTime
FROM
    fn_dblog(NULL, NULL) l
INNER JOIN
    (
    SELECT
        [Transaction ID]
    FROM 
        fn_dblog(NULL, NULL) 
    WHERE
        AllocUnitName LIKE @TableName + '%'
    AND
        Operation = 'LOP_DELETE_ROWS'
    ) deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
INNER JOIN
    sysusers u
ON  u.[sid] = l.[Transaction SID]

Resultado para mim (sysadmin)

UserName    TransactionStartTime
dbo 2019/08/09 17:14:10:450
dbo 2019/08/09 17:14:10:450