A little while back I came across this post: Identifying failed queries with extended events. It has a script to create an extended event that tracks failed queries. I cannot tell you how excited I was by this. Now, I can hear some of you thinking Well, I mean that’s interesting and all but why the big deal? I go through some variation of the following conversation at least once a week.
Dev: We are getting an error: SELECT permission was denied on the object MyObject.
Me: Ok, what instance, database, and account is getting the error?
Dev: The instance is InstanceA and we think the database is DatabaseA but we have no idea what the service account is.
Now, watch this.
-- Source: https://www.sqlservercentral.com/blogs/identifying-failed-queries-with-extended-events
--- Create Xevent
------ Be sure to change the filename and metadatafile
CREATE EVENT SESSION [FailedQueries] ON SERVER
ADD EVENT sqlserver.error_reported
(ACTION(sqlserver.client_app_name, sqlserver.client_hostname,
sqlserver.database_name, sqlserver.sql_text, sqlserver.username
--, sqlserver.tsql_stack /*include this if needed*/)
WHERE ([package0].[greater_than_int64]([severity], (10))))
ADD TARGET package0.event_file (SET
filename = N'E:Sql_EE_FailedQueries.xel'
,metadatafile = N'E:Sql_EE_FailedQueries.xem'
,max_file_size = (5)
,max_rollover_files = (10))
WITH (STARTUP_STATE = OFF) -- Change to ON if you want this to start when SQL starts.
GO
--- Start Xevent
ALTER EVENT SESSION [FailedQueries] ON SERVER
STATE = START;
GO
Now I open up a window to watch live data and have the user attempt to run their code:
And now I have confirmed that the database name is Kenneth_Test, the user is UserA and I even have the exact command they ran that is giving them an error. More than enough to solve the problem.
Of course there are a lot of other possible problems you might use this for, and on a stable production system I might even consider setting this up with an alert to send me any errors that are occurring. That way I can easily see if some new code has been installed that is having a problem, or even possibly if someone is trying to work out how to do something malicious.
I’ve got some additional pieces of code related to this on my GitHub. A script to turn off the session and another one to read information that’s already in the file the session is writing to. And of course you can get most if not all of this from the original poster (link above).