Vous êtes ici :
Cet ensemble de requêtes interroge les principaux paramètres de sécurité au niveau de l’instance : mode d’authentification, login sa, options de sécurité, configuration de l’errorlog, présence d’un audit de sécurité.
Sécurité de l'instance
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 |
-- Authentication SELECT CASE SERVERPROPERTY ('IsIntegratedSecurityOnly') WHEN 0 THEN 'mixed' WHEN 1 THEN 'windows integrated' END AS Authentication_mode GO -- SaLogin SELECT l.name, CASE WHEN l.name = 'sa' THEN 'no' ELSE 'yes' END as Renamed, s.is_policy_checked, s.is_expiration_checked, l.is_disabled FROM sys.server_principals AS l LEFT OUTER JOIN sys.sql_logins AS s ON s.principal_id = l.principal_id WHERE l.sid = 0x01 GO -- SecurityConfigurations SELECT configuration_id,name, value_in_use, CASE WHEN configuration_id IN (400, 16388, 16390, 16391) AND value_in_use = 1 THEN 1 ELSE 0 END AS Warning FROM sys.configurations WHERE configuration_id IN (16391, 400, 1562, 16386, 16385, 16388, 16390, 16393, 544, 1586, 1587) GO -- ErrorlogConfig DECLARE @AuditLevel INT DECLARE @ArchiveNumber int EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', @AuditLevel OUTPUT EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', @ArchiveNumber OUTPUT SELECT CASE WHEN @AuditLevel = 0 THEN 'None' WHEN @AuditLevel = 1 THEN 'Successful logins only' WHEN @AuditLevel = 2 THEN 'Failed logins only' WHEN @AuditLevel = 3 THEN 'Both failed and successful logins' END AS [ErrorlogAuditLevel] , @ArchiveNumber AS [ErrorlogArchives] GO -- SecurityAudit SELECT sa.name AS security_audit_name, sa.create_date,sa.modify_date, sa.type_desc, sa.on_failure_desc, sa.is_state_enabled ,fa.log_file_path, fa.log_file_name FROM sys.server_audits sa JOIN sys.server_file_audits fa ON sa.audit_id = fa.audit_id GO |
Table of Contents