Monitor table permission changes

Question

I need a solution to monitor table permission changes:

• how can I implement monitoring of every changes to granted permissions for a specific or any user on a specific table in SQL Server 2008 R2?

Answer 1

With SQL Server 2008 R2 (through current version), you can use SQL Server Audit which uses Extended Events.

Look at Understanding SQL Server Audit:

Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. You can use several methods of auditing for SQL Server, as described in Auditing (Database Engine). Beginning in SQL Server 2008 Enterprise, you can also set up automatic auditing by using SQL Server Audit.

SQL Server Audit is a combination of several parts:

• SQL Server Audit = on an instance, define Instance or Database level actions or groups of actions to monitor and destincation of the output
• Server Audit Specification = linked to one SQL Server Audit and is used to collect server level predifined groups of actions using Extended Events
• Database Audit Specification = linked to one SQL Server Audit and is used to collect database level groups of actions or audit events using Extended Events
• Target = the destination of the output to a file, Windows Security event log or Windows Application event log

The general process for creating and using an audit is as follow:

1. Create an audit and define the target.
2. Create either a server audit specification or database audit specification that maps to the audit.
3. Enable the audit specification.
4. Enable the audit.
5. Do something related to the audit...
6. Read the audit events by using the Windows Event Viewer, Log File Viewer, or the fn_get_audit_file function.

1. Create Audit:

USE [master]
GO
CREATE SERVER AUDIT [AuditTest] 
TO FILE 
(   FILEPATH = N'...\...'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
);

2. Create Audit Specification:

USE [YourDB]
GO
CREATE DATABASE AUDIT SPECIFICATION [SpecTest] 
FOR SERVER AUDIT [AuditTest] 
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
WITH (STATE = ON)

3. Enable Or Alter Audit Specification:

ALTER DATABASE AUDIT SPECIFICATION [SpecTest] WITH (STATE=OFF);
ALTER DATABASE AUDIT SPECIFICATION [SpecTest] ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
ALTER DATABASE AUDIT SPECIFICATION [SpecTest] WITH (STATE=ON);

4. Enable/Disable Audit

USE [master]
GO
-- disable
ALTER SERVER AUDIT [AuditTest2] WITH (STATE=OFF)
-- enable
ALTER SERVER AUDIT [AuditTest2] WITH (STATE=ON)

5. Change permission

DENY SELECT on dbo.testA to testLogin
REVOKE VIEW DEFINITION ON dbo.testA to testLogin
GRANT ALTER ON dbo.testA to testLogin

6. Read Audit Logs:

SELECT * FROM sys.fn_get_audit_file (N'C:\_data\AuditTest_*.sqlaudit', DEFAULT, DEFAULT);

Output:

event_time                  sequence_number action_id   succeeded   permission_bitmask                  is_column_permission    session_id  server_principal_id database_principal_id   target_server_principal_id  ...
2016-01-25 13:50:36.3210127 1               D           1           0x00000000000000000000000000000001  0                       57          260                 1                       271                         ...
2016-01-25 13:50:36.3210127 1               R           1           0x00000000000000000000000000000100  0                       57          260                 1                       271                         ...
2016-01-25 13:50:36.3210127 1               G           1           0x00000000000000000000000000000200  0                       57          260                 1                       271                         ...

Note that I am using SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP in place of DATABASE_OBJECT_PERMISSION_CHANGE_GROUP. You can read more from SQL Server Audit Action Groups and Actions toward the middle of the page:

This event is raised whenever a grant, deny, revoke is performed against a schema object. Equivalent to the Audit Schema Object GDR Event Class.