SQL Audit Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Audit Query

Author

Topic

djcarpen11
Starting Member

25 Posts

Posted - 2010-04-30 : 06:30:21

Can some one give me some help on the below? I have set up database auditing and wish to run the ALTER SERVER AUDIT command to reycling the auditfile I'm using, the problem I'm having is passing a variable to it.

This is the code I'm trying to run and it errors at the alter server audit comment.

DECLARE @AuditID int
DECLARE @MaxAuditID int

SET @AuditID = (Select MIN(audit_id) FROM sys.server_audits audit)
SET @MaxAuditid = (Select MAX(audit_id) FROM sys.server_audits audit)

WHILE @AuditID <> @maxauditid +1

IF @AuditID IN (SELECT AUDIT_ID FROM sys.server_audits)
BEGIN
DECLARE @AuditName nvarchar(50)

SET @AuditName = (SELECT Name FROM sys.server_audits WHERE audit_id = @AuditID)

ALTER SERVER AUDIT @AuditName WITH (STATE = OFF)
ALTER SERVER AUDIT @AuditName WITH (STATE = ON)

djcarpen11
Starting Member

25 Posts

Posted - 2010-04-30 : 06:42:13

sorry missed the end of my code!

DECLARE @AuditID int
DECLARE @MaxAuditID int

SET @AuditID = (Select MIN(audit_id) FROM sys.server_audits audit)
SET @MaxAuditid = (Select MAX(audit_id) FROM sys.server_audits audit)

WHILE @AuditID <> @maxauditid +1

IF @AuditID IN (SELECT AUDIT_ID FROM sys.server_audits)
BEGIN
DECLARE @AuditName nvarchar(50)

SET @AuditName = (SELECT Name FROM sys.server_audits WHERE audit_id = @AuditID)

Select @AuditName

ALTER SERVER AUDIT @AuditName WITH (STATE = OFF)
ALTER SERVER AUDIT @AuditName WITH (STATE = ON)

SET @AuditID = @AuditID + 1

END
ELSE
SET @AuditID = @AuditID + 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources