Jak dowiedzieć się, kto usunął niektóre dane SQL Server

Mój szef otrzymał wczoraj zapytanie od klienta z pytaniem, w jaki sposób można dowiedzieć się, kto usunął niektóre dane z bazy danych SQL Server (jeśli to ważne, jest to edycja ekspresowa).

Myślałem, że można to znaleźć w dzienniku transakcji (pod warunkiem, że nie zostało ono obcięte) - czy to prawda? A jeśli tak, to w jaki sposób możesz znaleźć te informacje?

Nie próbowałem fn_dblog na Express, ale jeśli jest dostępny, następujące operacje dadzą ci operacje usuwania:

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

Weź identyfikator transakcji dla transakcji, którymi jesteś zainteresowany, i zidentyfikuj identyfikator SID, który zainicjował transakcję:

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

Następnie zidentyfikuj użytkownika na podstawie SID:

SELECT
    *
FROM 
    sysusers
WHERE
    [sid] = @SID

Edycja: Łącząc to wszystko, aby znaleźć usunięcia w określonej tabeli:

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]

Jeśli baza danych znajduje się w trybie pełnego odzyskiwania lub jeśli masz kopie zapasowe dziennika transakcji, możesz spróbować je odczytać przy użyciu zewnętrznych czytników dzienników.

Możesz wypróbować ApexSQL Log (premium, ale ma darmową wersję próbną) lub SQL Log Rescue (bezpłatny, ale tylko SQL 2000).

jak mogli dowiedzieć się, kto usunął niektóre dane ze swojej bazy danych SQL Server

Mimo, że jest to odpowiedź, chciałem dodać, że SQL Server ma włączone śledzenie domyślne i można go użyć, aby dowiedzieć się, kto upuścił / zmienił obiekty.

Zdarzenia obiektowe

Do zdarzeń obiektowych należą: Obiekt zmieniony, Obiekt utworzony i Obiekt usunięty

Uwaga: SQL Server domyślnie ma 5 plików śledzenia, po 20 MB każdy i nie ma znanej obsługiwanej metody zmiany tego. Jeśli masz zajęty system, pliki śledzenia mogą przewijać się o wiele za szybko (nawet w ciągu kilku godzin) i możesz nie być w stanie uchwycić niektórych zmian.

Doskonały przykład można znaleźć: Domyślny ślad w SQL Server - moc kontroli wydajności i bezpieczeństwa

Możesz wypróbować tę procedurę, aby wykonać kwerendę w plikach kopii zapasowej dziennika i dowiedzieć się, w których plikach kopii zapasowej dziennika znajduje się konkretna wartość kolumny tabeli.

Aby znaleźć użytkownika, po znalezieniu w kopii zapasowej dziennika wartości, która ostatnio istniała, możesz przywrócić bazę danych do momentu utworzenia kopii zapasowej dziennika, a następnie postępować zgodnie z odpowiedzią Marka Storey-Smitha .

Niektóre warunki wstępne

• wiedzieć, z jakich wartości usunięto kolumny
• Są w modelu pełnego odzyskiwania i wykonują kopie zapasowe dziennika
• masz kopie zapasowe dzienników lub identyfikatorów, na przykład podczas korzystania z rozwiązania Oli Hallengren

Zrzeczenie się

To rozwiązanie jest dalekie od wodoodporności i wymaga dużo więcej pracy.

Nie został przetestowany w środowiskach wielkoskalowych, a nawet w innych środowiskach oprócz kilku małych testów. Bieżący przebieg miał miejsce na SQL Server 2017.

Możesz skorzystać z poniższej procedury od Muhammada Imrana , którą zmodyfikowałem do pracy z zawartością kopii zapasowych dziennika zamiast zawartości dziennika żywej bazy danych.

W ten sposób technicznie nie przywracasz, ale zamiast tego zrzucasz zawartość dziennika do tabeli tymczasowej. Prawdopodobnie będzie nadal powolny i jest bardzo otwarty na błędy i problemy. Ale to może działać, teoretycznie ™.

Procedura przechowywana używa nieudokumentowanej fn_dump_dblogfunkcji do odczytu plików dziennika.

Środowisko testowe

Rozważ tę bazę danych, w której wstawiamy niektóre wiersze, wykonujemy 2 kopie zapasowe dziennika, a przy trzeciej kopii zapasowej dziennika usuwamy wszystkie wiersze.

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

Procedura

Można znaleźć i pobrać procedury przechowywanej tutaj .

Nie mogłem go tutaj dodać, ponieważ jest on większy niż limit postaci, i uczyniłbym tę odpowiedź jeszcze mniej wyraźną niż jest.

Oprócz tego powinieneś być w stanie uruchomić procedurę.

Uruchamianie procedury

Przykładem tego jest dodanie wszystkich moich plików dziennika ( 4) do procedury składowanej i uruchomienie procedury w poszukiwaniu wartości1

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

To daje mi:

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

Gdzie możemy znaleźć datę ostatniej operacji value1, usuń w log3.trn.

Więcej danych testowych, dodając tabelę z różnymi kolumnami

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

Zmiana nazw plików dziennika i ponowne wykonanie procedury

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

Wynik

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

Nowy przebieg, szukanie liczby całkowitej ( 2) w val3kolumniedbo.WrongDeletes2

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

Wynik

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

Stosując Mark Storey-Smith „s odpowiedź

Wiemy teraz, że stało się to w trzecim pliku dziennika, przywróćmy do tego momentu:

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

Uruchomienie ostatniego zapytania w odpowiedzi

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]

Wynik dla mnie (sysadmin)

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