时间戳

首页 > 解决方案 > 为什么某些 DATABASE_ROLE_MEMBER_CHANGE_GROUP 审计事件会触发，而其他则不会？

sql-server - 为什么某些 DATABASE_ROLE_MEMBER_CHANGE_GROUP 审计事件会触发，而其他则不会？

问题描述

该语句触发和审计事件

EXEC sp_addrolemember N'db_data', N'BRANCH\NY-Users'

但是这个语句不会触发任何审计事件

EXEC master..sp_addsrvrolemember @loginame = N'BRANCH\MY_APP_User', @rolename = N'securityadmin' 

我要求 DBA 在数据库中创建这些审计。

USE master;
GO
CREATE SERVER AUDIT IT_Security_server_audit
TO APPLICATION_LOG
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
GO
 Alter Server Audit IT_Security_server_audit with(State=ON)
GO
CREATE SERVER AUDIT SPECIFICATION IT_Security_Server_Audit_Specification
FOR SERVER AUDIT IT_Security_server_audit
     ADD ( SUCCESSFUL_LOGIN_GROUP )
    ,ADD ( AUDIT_CHANGE_GROUP )
    ,ADD ( BACKUP_RESTORE_GROUP )
    ,ADD ( DATABASE_CHANGE_GROUP )
    ,ADD ( DATABASE_OWNERSHIP_CHANGE_GROUP )
    ,ADD ( BROKER_LOGIN_GROUP)
    ,ADD ( DBCC_GROUP )
    ,ADD ( LOGIN_CHANGE_PASSWORD_GROUP )
    ,ADD ( APPLICATION_ROLE_CHANGE_PASSWORD_GROUP )
    ,ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
    ,ADD (DATABASE_PERMISSION_CHANGE_GROUP)
    ,ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP)
WITH ( STATE = ON);
GO
--Create the database Audit spec
CREATE DATABASE AUDIT SPECIFICATION  IT_Security_Database_Audit_Specification
FOR SERVER AUDIT IT_Security_server_audit
       ADD ( AUDIT_CHANGE_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_PRINCIPAL_CHANGE_GROUP )
       ,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )
       ,ADD ( DBCC_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 ( DATABASE_LOGOUT_GROUP )
       ,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )
       ,ADD ( USER_DEFINED_AUDIT_GROUP )
       ,ADD ( SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP )
      -- New GROUPS
       ,ADD ( DATABASE_OWNERSHIP_CHANGE_GROUP )
       ,ADD ( DATABASE_PERMISSION_CHANGE_GROUP )
       ,ADD ( LOGIN_CHANGE_PASSWORD_GROUP )

WITH ( STATE = ON);
go

从审计事件的角度来看，“sp_addrolemember”和“sp_addsrvrolemember”有什么区别？我上面的审核是否没有涵盖这两种情况？

标签： sql-serversql-server-2008sql-server-2012sql-server-2008-r2

解决方案

我找到了！我缺少 SERVER_ROLE_MEMBER_CHANGE_GROUP。

ALTER SERVER AUDIT SPECIFICATION  IT_Security_Server_Audit_Specification  
FOR SERVER AUDIT IT_Security_server_audit
        ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP);  
GO

推荐阅读