SQL Audit - capture permission failures

Question

Fellow SQL Dba's

I have a need to capture ANY permission failure on the instance. I have been using SQL Audit for quite sometime now but I cannot find a way to ONLY capture the permission failures. Login failures are easy, but when someone has access to a db wihtout ddl permissions and tries to create a table, and it has permission failure, I need to capture the attempt. I have tried Schema_object_access_group at the server level audit specification and also tried the same at a database audit specification. Nope, nothing is capturing this message.

Any other options or something I am missing, please let me know. Profiler is not an option. ;)

Many thanks.

MG

Answer 1

One option is to set up an Extended Events session that captures exceptions, then you would have to filter the output to capture the permission errors.

I have an article on my web site to set up such an Xevent-session and I also present a view to extract information from the session, https://www.sommarskog.se/Short%20Stories/trace-exceptions.html.

Answer 2

Hi MarkGordon-2676,

You can try to use extended event to create an event session on the server to collect the sqlserver.error_reported and sqlserver.errorlog_written to capture error message. Please refer to this article which might help.
Or you can consider capturing the "User Error Message" event in the SQL Server profiler and filtering the Error column as you see fit. Error 229 is permission denied. Please refer to this thread which might help.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.