database access for auditing database

Question

question

Dudarino asked Jun 26, '18

database access for auditing database

We create a Login account for each database on our server. I have been tasked with adding Auditing to all of our databases and my plan was to create a separate database and store all auditing information there for whenever a stored procedure fails. Problem is that the stored procedure is executed by an account that has access to just one database.

Is there a way to easily grant access to all user accounts to the Audit database or can I create a trigger that executes statements using a specific user account which will have access to my auditing database?

security login access

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-06-28T07:57:49Z

WRBI answered Jun 28, '18

If you're only inserting into the Audit database when a SPROC fails, I would use TRY/CATCH blocks to capture the error and any details that you feel relevant. I'd do this by creating a SPROC in the Audit database that you can pass the into through variables.

In the CATCH block I would use the ERROR functions to capture the data and anything else you want to add in there:

ERROR_LINE() - the line the error happened on.
ERROR_MESSAGE() - the actual error message.
ERROR_NUMBER() - the error number.
ERROR_PROCEDURE() - the name of the SPROC that failed.
ERROR_SEVERITY() - the severity code of the error.
ERROR_STATE() - the state of the error.

If its for capturing all usage of SPROCs then have a look at this post (it's a lengthy article with various ways to achieve the desired result, didn't fancy copying the whole article into here):

MSSQLTips - Several Methods to collect SQL Server Stored Procedure Execution History

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question