Share via

SQL Audit - capture permission failures

Mark Gordon 906 Reputation points
2021-07-07T13:26:42.98+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments
Accepted answer
  1. Erland Sommarskog 107.1K Reputation points
    2021-07-07T22:00:19.827+00:00

    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.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. AmeliaGu-MSFT 13,971 Reputation points Microsoft Vendor
    2021-07-08T06:27:00.363+00:00

    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.