Help dynamically setting up auditing using T-SQL

Question

Post reply

Help dynamically setting up auditing using T-SQL

_Beetlejuice

Ten Centuries

Points: 1064
More actions
September 25, 2010 at 3:20 am

#228743

Hi
I have been asked to set up auditing on our SCOM 2007 Audit collection Services database. The auditing needs to be on the tables where the event logs are written to.
The problem I have is each day a new table is created dynamically which for example could be called dbo.dtEvent_58e362d9_3255_4ea3_b820_068f3a56b935
You find out what the daily table is called by perfroming the following query
select [PartitionId] from dbo.dtPartition where status = 0
The result of that query today is 58e362d9_3255_4ea3_b820_068f3a56b935
My idea is to set up a sql agent job each day which will dynamically calculate the days table and then add an audit policy onto the table in case anyone tries to edit it.
I have used the following code
USE [OperationsManagerAC]
GO
declare @sql varchar(max)
declare @partition varchar(36)
declare @prefix varchar(12)
declare @column varchar(48)
declare @today datetime
set @partition = (select [PartitionId] from dbo.dtPartition where status = 0)
set @prefix = 'dbo.dtEvent_'
set @column = @prefix + @partition
set @today = GETDATE()
CREATE DATABASE AUDIT SPECIFICATION [@today]
FOR SERVER AUDIT [Audit-20100909-140542]
ADD (UPDATE ON OBJECT::[@column] BY [dbo])
GO
When I execute this i get the following message
Msg 15151, Level 16, State 1, Line 16
Cannot find the object '@column', because it does not exist or you do not have permission.
If anyone could give me some help with tihs it would be gratly appreciated
Best Regards
Chris

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it's fairly straightforward;

the OBJECT:: does not allow a variable name i think. as a result, you just have to use dynamic sql to do the same job.

declare @sql varchar(max)

declare @partition varchar(36)

declare @prefix varchar(12)

declare @column varchar(48)

declare @today datetime

set @partition = (select [PartitionId] from dbo.dtPartition where status = 0)

set @prefix = 'dbo.dtEvent_'

set @column = @prefix + @partition

set @today = GETDATE()

declare @sql varchar(1000)

SET @sql ='

CREATE DATABASE AUDIT SPECIFICATION [@today]

FOR SERVER AUDIT [Audit-20100909-140542]

ADD (UPDATE ON OBJECT::[' + @column + '] BY [dbo])'

print @sql

exec(@sql)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

_Beetlejuice Ten Centuries Points: 1064 More actions · Answer 2

Hi Lowell

That worked a treat!!!

Thank you very much

Chris