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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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:
The general process for creating and using an audit is as follow:
- Create an audit and define the target.
- Create either a server audit specification or database audit specification that maps to the audit.
- Enable the audit specification.
- Enable the audit.
- Do something related to the audit...
- 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);
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.