Tracking grant violations

Question

We are changing one of our application user logins from being dbowner to a more granular permission scheme--finally.

Is there a way from within SQL Server 2016 that I can track permission violations? That is, this new user cannot SELECT from table dbo.TableName so that I can review it and determine if it is appropriate or not.

I know I can trap errors from the program side of things and log them, but I would rather not rely on that if the database can log that for me.

Answer 1

You can use Extended Events™ to accomplish this. The error_reported event allows you to log a row to a file every time a certain error occurs. You can add additional information to what is logged such as username and client_hostname. Here is some example T-SQL to get you started that filters on only error code 262:

CREATE EVENT SESSION [error_262_to_file] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.username)
    WHERE ([error_number]=(262)))
ADD TARGET package0.event_file(SET filename=N'C:\XE\error_262_to_file.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Suppose one of my most troublesome users logs in with his sean_gallardy login. I don't trust this user so I gave him very limited permissions. If he tries to view a query plan he gets the following error message in SSMS:

Msg 262, Level 14, State 4, Line 1

SHOWPLAN permission denied in database 'master'.

Here is what is logged to the extended event file in response: