How to Check SQL Server View Permissions Changes


Native Auditing vs. Netwrix Auditor for SQL

We never share your data. Privacy Policy
Native Auditing Netwrix Auditor for SQL Server
Steps
  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.

SQL view permissions changes in Excel

  1. Run Netwrix Auditor → Navigate to “Search” → Adjust the following filters:
    • Filter – "Data Source"
      Operator – "=(Equals)"
      Value – "SQL Server"
    • Filter – "Action"
      Operator – "Equals"
      Value – "Modified"
    • Filter – "ObjectType" Operator – "Equals" Value – "View"
  2. Click "Search".

AuditIntelligence Search Results from Netwrix Auditor

  1.  
  2.  
  3. To save the report, click the "Export" button → Choose a format, such as PDF → Click “Save as” → Choose a location to save it.

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.

There’s a simple and reliable way to stay abreast of all changes to SQL Server permissions and database roles and thereby protect your critical data. Netwrix Auditor for SQL Server delivers 360-degree visibility into what’s going on across your SQL Server, including not just changes to access rights, but also all SQL logins and changes to underlying tables, stored procedures and more. Forget about manually building Transact-SQL scripts to generate reports. The easy-to-read reports provided by Netwrix Auditor show each action and who made it and when, so you can quickly identify improper changes and unauthorized logins and investigate them quickly to safeguard your sensitive data.