question

Dudarino avatar image
Dudarino asked

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?

securityloginaccess
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
WRBI avatar image
WRBI answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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