3

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.

6

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:

enter image description here

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.