June 3, 2014 at 2:56 pm
Is there a query that I can run which will give me a list of users that have tried to use SQL resources for which they do not have permissions?
-e
June 3, 2014 at 10:52 pm
I do not think that information is captured anywhere by default. An Extended Events session or Server-Side Trace could capture the error information and tie it to a specific Login.
Another, maybe roundabout, way to achieve would be to enable the logging of all error messages that pertain to permissions errors you're interested in, e.g. 229, which would add them to the SQL Error Log when they occurred along with the session ID that generated the message. You would still need a way to cross-reference the session ID to a specific Login which again would require Extended Events or Trace, but it may be easier to have SQL log the errors itself and just track the server login event in EE or Trace to get back to a Login name.
SELECT * FROM sys.messages WHERE text LIKE '%permission%' AND language_id = 1033;
-- enable the logging of specific messages to the SQL Error Log
EXEC sys.sp_altermessage
@message_id = 229, -- int
@parameter = 'with_log', -- sysname
@parameter_value = 'true' -- varchar(5)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply