5

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?
4

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.

  • Julien, I have created Sql Server Audit + Database Audit specification on "DATABASE_OBJECT_PERMISSION_CHANGE_GROUP" but there is no entry in the windows Eventlog appearing as soon I GRANT permission to a table. – Magier Jan 25 '16 at 10:16
  • Have you enabled both qudit and audit specification? Is Destination of audit set up to windows event log and are you looking at the right location in even log? – Julien Vavasseur Jan 25 '16 at 10:19
  • 1 yes, 2 yes, 3 yes and 4 I am not sure, I look at "Application": CREATE SERVER AUDIT [Audit1] TO APPLICATION_LOG WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ,AUDIT_GUID = '44413140-9827-49aa-90d1-7ff5726a1123' ) CREATE DATABASE AUDIT SPECIFICATION [A1] FOR SERVER AUDIT [Audit1] ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP) WITH (STATE = ON) – Magier Jan 25 '16 at 10:22
  • Se above. I tried app/security and F5ed. There show up only disabled/enabled evetnts of the audit. I meanwhile changed to FILE as target. But the file is a binary. I dragged it to SSMS to open it, but this ended in an unhandled exception of SSMS. – Magier Jan 25 '16 at 10:30
  • 1
    sorry, I didn't copy and pasted the STATE=ON for audit. I will add it now to the answer although you already found it. – Julien Vavasseur Jan 26 '16 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.