Vous êtes ici :
Plutôt que de journaliser les connexions dans l’errorlog, activer l’audit de sécurité : plus sûr, moins polluant et plus complet…
Transact-SQL
|
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 |
/*------------------------------------------------------------------- [SCRIPT] Configure security audit [DATABASE] master [Date] 20210815 [DESCRIPTION] configure security audit [MAJ PAR] DATAFLY - Arian Papillon -------------------------------------------------------------------*/ DECLARE @sql NVARCHAR(MAX); DECLARE @errorlogpath NVARCHAR(1000); SELECT @errorlogpath = LEFT(path, LEN(path) - 1) FROM sys.dm_os_server_diagnostics_log_configurations; -- Create server audit IF EXISTS (SELECT * FROM sys.server_audits WHERE name = 'Security-Audit') PRINT 'Security Audit already existing, not modified'; ELSE BEGIN SET @sql = N'CREATE SERVER AUDIT [Security-Audit] TO FILE ( FILEPATH = ''' + @errorlogpath + N''' ,MAXSIZE = 100 MB ,MAX_ROLLOVER_FILES = 10 ,RESERVE_DISK_SPACE = OFF ) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);'; EXEC (@sql); -- Add server audit specifications SET @sql = N' CREATE SERVER AUDIT SPECIFICATION [Security-Audit-Specification] FOR SERVER AUDIT [Security-Audit] -- ADD (SUCCESSFUL_LOGIN_GROUP), ADD (FAILED_LOGIN_GROUP) , ADD (AUDIT_CHANGE_GROUP) , ADD (SERVER_PRINCIPAL_CHANGE_GROUP) , ADD (LOGIN_CHANGE_PASSWORD_GROUP) , ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP) , ADD (SERVER_PERMISSION_CHANGE_GROUP); -- Audit activation ALTER SERVER AUDIT [Security-Audit] WITH (STATE = ON); ALTER SERVER AUDIT SPECIFICATION [Security-Audit-Specification] WITH (STATE = ON);'; EXEC (@sql); END; GO |
Table of Contents