Ustalanie, w jaki sposób nastąpiła zmiana schematu?

Wczoraj stało się coś złego.

Widok utworzony jakiś czas temu został zmodyfikowany przez kogoś, kto ostatecznie złamał raporty. Niestety. ktoś (świadomie lub nieświadomie) dokonał tej modyfikacji w bazie danych PRODUKCJA.

Moje pytanie: Czy istnieje sposób (skrypt / oprogramowanie / freeware itp.), Dzięki któremu możemy dowiedzieć się, kto (nazwa użytkownika) dokonał tej modyfikacji, tak że mogę odwołać dostęp do produkcyjnej bazy danych dla tego użytkownika.

Jeśli moje pytanie jest niejasne, proszę o komentarz.

Jest on rejestrowany w domyślnym zapisie, więc dopóki jest włączony i nie został przeniesiony w międzyczasie, powinien pojawić się w raporcie „Historia zmian schematu”.

Aby uzyskać dostęp do tego w Management Studio, kliknij prawym przyciskiem myszy bazę danych, a następnie z menu kontekstowego wybierz Reports -> Standard Reports -> Schema Changes History

Aby pobrać te same informacje za pośrednictwem TSQL, możesz użyć

SELECT StartTime
       ,LoginName
       --,f.*
FROM   sys.traces t
       CROSS APPLY fn_trace_gettable(REVERSE(SUBSTRING(REVERSE(t.path),
                                                       CHARINDEX('\', REVERSE(t.path)), 
                                                       260)
                                             ) + N'log.trc', DEFAULT) f
WHERE  t.is_default = 1
       AND ObjectName = 'FOO'
       AND EventClass IN (46, /*Object:Created*/
                          47, /*Object:Dropped*/
                          164 /*Object:Altered*/ )

Martin wskazał już na najlepszą ścieżkę - ślad audytu administracyjnego, który jest zwykle włączony (chyba że został wyraźnie wyłączony). Jeśli nie możesz znaleźć informacji w śladzie administratora (został wyłączony lub został ponownie przetworzony), możesz odzyskać informacje z kopii zapasowych dziennika. Ponieważ jest to produkcyjna baza danych, zakładam, że masz regularny cykl tworzenia kopii zapasowych, z okresowymi pełnymi kopiami zapasowymi i kopiami zapasowymi dziennika. Konieczne będzie przywrócenie bazy danych na osobnym serwerze mniej więcej w czasie zdarzenia, tak aby DDL znajdował się w bieżącym przywróconym dzienniku. Następnie jest prosta kwestia używania fn_dblog()i sprawdzania dziennika.

Jednym ze sposobów jest przejście przez operacje rozpoczynania transakcji:

select [Begin Time], [Transaction Name], [Transaction SID], * 
from fn_dblog(null, null)
where Operation = 'LOP_BEGIN_XACT';

Jeżeli ALTER VIEWzostał wydany w ramach samodzielnej transakcji (tj. Nie jest otoczonyBEGIN TRANSACTION / COMMIT), rozpocznie się transakcja o nazwie CreatProc transaction. Poszukaj go, a [Transaction SID]jest to identyfikator SID logowania, który chcesz.

Inną możliwością jest poszukiwanie transakcji, która uzyskała SCH_M w wybranym widoku:

select [Lock Information], * 
from fn_dblog(null, null)
where [Lock Information] like '%' + cast(object_id('...') as varchar(10))+'%'
and [Lock Information] like '%LOCK_SCH_M%'
go

Zauważ, że jeśli widok został zmieniony przez DROP, a następnie CREATE, identyfikator obiektu prawdopodobnie został zmieniony, ale przynajmniej dostaniesz transakcję, która ostatnio wykonała CREATE (bieżący identyfikator obiektu widoku w przywróconej bazie danych). Z identyfikatorem transakcji wróć i pobierz informacje o rozpoczęciu transakcji:

select [Begin Time], [Transaction Name], [Transaction SID], *
from fn_dblog(null, null)
where [Transaction ID] = '...'
and Operation = 'LOP_BEGIN_XACT';

[Transakcja SID] jest znowu twoim facetem. Służy SUSER_SNAMEdo pobierania nazwy logowania z identyfikatora SID logowania. Jeśli SID to 0x01, oznacza to, że login był sa, co oznacza, że ​​każda osoba, która zna sahasło, mogła to zrobić.

Nie, chyba że zalogowałeś go za pomocą wyzwalacza DDL lub podobnego

Chcesz sprawdzić, kto ma uprawnienia ALTER do tej bazy danych lub członkostwo w roli sysadmin / db_owner / ddl_admin. Byłoby to lepsze jako przegląd ogólny niż polowanie na czarownice. Prawdopodobnie są też inne osoby, które mają prawo dokonywać niezatwierdzonych i nieautoryzowanych zmian

Jeśli jeszcze tego nie zrobiłeś, możesz zajrzeć do raportu Historia zmian schematu dostępnego w SQL Server Management Studio. Wygląda na to, że SQL Server domyślnie rejestruje zmiany ( domyślny ślad ) i powinieneś móc przeglądać te dane za pomocą tego raportu. Jedyną niefortunną rzeczą jest to, że te pliki śledzenia są automatycznie usuwane / zwijane w miarę upływu czasu, więc dane mogą już zniknąć. Powodzenia!