How to Check SQL Server View Permissions Changes

  • Article

Track Changes to View Permissions to Minimize the Risk of Data Overexposure

When a database (DB) owner grants SELECT object permissions to a view that includes a selection of securable data taken from specific tables, the grantee becomes able to read the information. Therefore, it’s essential to ensure that each database user is granted object permissions, such as SQL Server view permissions, properly — via role membership and with all necessary approvals. Otherwise, users can read more data than they should be able to, which can threaten data security and result in the exposure of sensitive data.

1. Run SQL Management Studio and execute the following T-SQL code to create and enable a SQL Server audit and a SQL Server audit specification, adjusting the path to the logs as needed:

USE master ;
GO
-- Create the server audit.
CREATE SERVER AUDIT AuditSQL
    TO FILE ( FILEPATH ='\\SQL\Audit\' )
        WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE );
GO
-- Create the server audit specification.
CREATE SERVER AUDIT SPECIFICATION Change_Object_Permissions
FOR SERVER AUDIT AuditSQL
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP);
GO
-- Enable the server audit.
ALTER SERVER AUDIT AuditSQL
WITH (STATE = ON);
GO
-- Enable the server audit specification.
ALTER SERVER AUDIT SPECIFICATION Change_Object_Permissions
WITH (STATE = ON);
GO
-- Move it to the target database.
USE DBNAME;
GO
-- Create and enable the database audit specification.
CREATE DATABASE  AUDIT SPECIFICATION Change_Object_Permissions
FOR SERVER AUDIT AuditSQL
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
WITH (STATE = ON);
GO

2. To view object permissions changes, execute the following code in SQL Management Studio, again adjusting the path to the logs as needed:

SELECT * FROM sys.fn_get_audit_file ('SQL\\Audit\*',default,default)
WHERE action_id like 'G'

3. Open the file produced by the script in MS Excel.

https://img.netwrix.com/howtos/Excel_4.png

Originally posted: https://www.netwrix.com/how_to_check_sql_server_view_permissions_changes.html