Generate script
-
USE [DatabaseA]
GO
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-DatabaseA]
FOR SERVER AUDIT [ServerAuditName]
ADD (INSERT ON DATABASE::[DatabaseA] BY [dbo]),
ADD (SELECT ON DATABASE::[DatabaseA] BY [dbo]),
ADD (DELETE ON DATABASE::[DatabaseA] BY [dbo])
WITH (STATE = OFF)
GO
I have 100+ databases and would like to generate a script which I can execute in one go instead of running this a 100 times by changing database names in the script every time I create a database audit specification for each database. Advise is appreciated?USE [DatabaseA]
GO
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-DatabaseA]
FOR SERVER AUDIT [ServerAuditName]
ADD (INSERT ON DATABASE::[DatabaseA] BY [dbo]),
ADD (SELECT ON DATABASE::[DatabaseA] BY [dbo]),
ADD (DELETE ON DATABASE::[DatabaseA] BY [dbo])
WITH (STATE = OFF)
GO
I have 100+ databases and would like to generate a script which I can execute in one go instead of running this a 100 times by changing database names in the script every time I create a database audit specification for each database. -
How about
/* generate DDL */
exec sp_msforeachdb '
Select ''
USE [?]
GO
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-DatabaseA]
FOR SERVER AUDIT [ServerAuditName]
ADD (INSERT ON DATABASE::[?] BY [dbo]),
ADD (SELECT ON DATABASE::[?] BY [dbo]),
ADD (DELETE ON DATABASE::[?] BY [dbo])
WITH (STATE = OFF)
GO
''
'Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks @Johan.
I settled with this...
CREATE TABLE #Results (
abc VARCHAR(MAX)
)
INSERT INTO #Results
exec sp_msforeachdb '
Select ''
USE [?]
GO
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification ?]
FOR SERVER AUDIT [ServerAuditName]
ADD (SELECT ON DATABASE::[?] BY [dbo]),
ADD (INSERT ON DATABASE::[?] BY [dbo]),
ADD (DELETE ON DATABASE::[?] BY [dbo])
WITH (STATE = OFF)
GO
''
'
select * from #Results - wrote:
I also have the same case, thanks for this solution!
Uh-huh... Sounds like a precursor to spam.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply