Is some type of restart needed after issuing ALTER DATABASE AUDIT SPECIFICATION to take affect ?

Question

Hello I had created an existing SQL Server [DATABASE AUDIT SPECIFICATION] within a particular User DB running that tracks SELECT statements on some Tables that's been up and running for quite some time. We would like to now add another Table to this particular existing [DATABASE AUDIT SPECIFICATION] that's within this User DB. If I run the following: USE [UserDB] GO ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-UserDB] ADD (SELECT ON OBJECT::[dbo].[AnotherTableNameToBeAdded] BY [public]) GO Believe some type of restart (perhaps Disable & Re-enable) needs to be done before new Table gets added in tracking to take affect. Would it be within the User DB level by Disable/Re-enable [DatabaseAuditSpecification-UserDB] or at the SQL Server level within the [Security] -> [Audits] folder that it's related to or both...and if both which should be done first in sequentially order? Or please let me know if I'm off and missing something else like the entire SQL Service/Windows OS needs to be rebooted (I wouldn't think need to go to that extreme). Thanks in advance.

Answer 1

Not so much a restart but there is this in the doco: "You must set the state of an audit specification to the OFF option in order to make changes to a database audit specification. If ALTER DATABASE AUDIT SPECIFICATION is executed when an audit is enabled with any options other than STATE=OFF, you will receive an error message."

Answer 2

Hi @techresearch7777777

Is some type of restart needed after issuing ALTER DATABASE AUDIT SPECIFICATION to take affect ?

It takes effect immediately when you finish configuration options and select OK**.**

Besides, you should know the limitations and restrictions.

Database audit specifications are non-securable objects that reside in a given database. When a database audit specification is created, it's in a disabled state.

When you're creating or modifying a database audit specification in a user database, don't include audit actions on server-scope objects, like the system views. If you include server-scoped objects, the audit will be created. But the server-scoped objects won't be included, and no error will return. To audit server-scoped objects, use a database audit specification in the master database.

Database audit specifications reside in the database where they're created, except for the TempDB system database.

See this doc: Create a server audit and database audit specification.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.