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
  • This was removed by the editor as SPAM

  • matilda 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)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 5 (of 5 total)

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