DevOps környezetben vagy éles környezetben érdemes auditot beállítani. Saját érdekünkben érdemes tudni, hogy ki nyúlt bele és mit csinált!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
CREATE SERVER AUDIT [Audit-20190110] TO FILE ( FILEPATH = N'H:\Audit\' ,MAXSIZE = 100 MB ,MAX_FILES = 500 ,RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 3000 ,ON_FAILURE = CONTINUE ) ALTER SERVER AUDIT [Audit-20190110] WITH (STATE = ON) GO USE [master] GO CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20190110] FOR SERVER AUDIT [Audit-20190110] -- ADD (DATABASE_OBJECT_ACCESS_GROUP), ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), ADD (BACKUP_RESTORE_GROUP), ADD (AUDIT_CHANGE_GROUP), ADD (FAILED_DATABASE_AUTHENTICATION_GROUP), ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP), ADD (FAILED_LOGIN_GROUP), ADD (DATABASE_MIRRORING_LOGIN_GROUP), -- ADD (LOGOUT_GROUP), ADD (DATABASE_CHANGE_GROUP), ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), ADD (SCHEMA_OBJECT_CHANGE_GROUP), ADD (SERVER_OBJECT_CHANGE_GROUP), ADD (SERVER_OPERATION_GROUP), ADD (LOGIN_CHANGE_PASSWORD_GROUP), ADD (SERVER_STATE_CHANGE_GROUP), ADD (DATABASE_OWNERSHIP_CHANGE_GROUP), ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (USER_CHANGE_PASSWORD_GROUP) WITH (STATE = ON) GO --Create the database Audit spec CREATE DATABASE AUDIT SPECIFICATION [dbaudit] FOR SERVER AUDIT [Audit-20190110] ADD ( AUDIT_CHANGE_GROUP ) ,ADD ( APPLICATION_ROLE_CHANGE_PASSWORD_GROUP) ,ADD ( BACKUP_RESTORE_GROUP ) ,ADD ( DATABASE_CHANGE_GROUP ) ,ADD ( DATABASE_OBJECT_CHANGE_GROUP ) ,ADD ( DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP ) ,ADD ( DATABASE_OBJECT_PERMISSION_CHANGE_GROUP ) ,ADD ( DATABASE_PERMISSION_CHANGE_GROUP ) ,ADD ( DATABASE_PRINCIPAL_CHANGE_GROUP ) ,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP ) ,ADD ( DBCC_GROUP ) ,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP ) ,ADD ( SCHEMA_OBJECT_CHANGE_GROUP ) ,ADD ( SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP ) ,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP ) ,ADD ( SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP ) -- 2012-newer allowed events ,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP ) ,ADD ( USER_DEFINED_AUDIT_GROUP ) --,ADD ( SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP ) WITH ( STATE = ON); GO SELECT event_time ,action_id ,session_server_principal_name AS UserName ,server_instance_name ,database_name ,schema_name ,object_name ,statement FROM sys.fn_get_audit_file('H:\Audit*.sqlaudit', DEFAULT, DEFAULT) -- WHERE action_id IN ( 'SL', 'IN', 'DR', 'LGIF' , '%AU%' ) |