Batali sistemski log, kreiraj svoj u koji ces trpati ono sto tebi treba, dakle za pocetak:
Code:
CREATE SERVER AUDIT [MojAudit001]
TO FILE (FILEPATH = N'C:\Temp') WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
GO
Promeni folder samo gde hoces da ti idu logovi.
Sad treba da mu kazes sta da ti trpa u logove a to radis kroz "Server Audit Specification" i "Database Audit Specifications" na nivou pojedinacnih baza.
Npr server-level za sve logine i logoutove u mojaudit...
Code:
CREATE SERVER AUDIT SPECIFICATION [LoginSpecifikacija] FOR SERVER AUDIT [MojAudit001]
ADD (SUCCESSFUL_LOGIN_GROUP), ADD (FAILED_LOGIN_GROUP), ADD (LOGOUT_GROUP);
GO
Recimo hocemo da vidimo u bazi XYZ ko menja permissions i ko radi select nad tabelom tabela, dodamo i to u log
Code:
USE [XYZ]
GO
CREATE DATABASE AUDIT SPECIFICATION [XYZSpecifikacijaZaPermission] FOR SERVER AUDIT [MojAudit001]
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SELECT ON OBJECT::[dbo].[tabela] BY [dbo])
GO
Na kraju aktiviras sve to
Code:
ALTER SERVER AUDIT [MojAudit001] WITH (STATE = ON);
GO
ALTER SERVER AUDIT SPECIFICATION [LoginSpecifikacija] WITH (STATE = ON);
GO
ALTER SERVER AUDIT SPECIFICATION [XYZSpecifikacijaZaPermission] WITH (STATE = ON);
GO
Sve ovo inace moze iz odgovarajucih menija u server menadzeru.
I sad ce sve te informacije da idu u log koji mozes da vidis na isti nacin na koji gledas sistemski, samo koristis fn_get_audit_file za citanje istog:
Code:
select action_id, session_server_principal_name, database_principal_name, statement
from sys.fn_get_audit_file ('C:\Temp\MojAudit*',default,default)
WHERE event_time > GETDATE() - 1
order by event_time desc
npr za dva granta (G), select (SL) i login (LGIS) s tim da imas jos dosta polja u logu koja ti daju vise detalja o akciji.
Code:
action_id session_server_principal_name database_principal_name statement
----------------------------------------------------------------------------------------------------
G SOLARIS\Administrator dbo GRANT REFERENCES ON [dbo].[tabela] TO [guest]
G SOLARIS\Administrator dbo GRANT ALTER ON [dbo].[tabela] TO [guest]
SL SOLARIS\Administrator dbo SELECT * FROM [XYZ].[dbo].[tabela]
LGIS -- network protocol: LPC ...