Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: permissions problems with trigger script

    CREATE TRIGGER trg_audit_version
    ON dbo.syscomments
    FOR INSERT, UPDATE, DELETE
    AS
    SELECT * INTO versionaudit
    FROM @@version;
    GO

    SE sqlsvr_audit;
    GO
    --
    CREATE TRIGGER trg_audit1
    ON dbo.syscomments
    FOR INSERT, UPDATE, DELETE
    AS
    SELECT * INTO audit1
    FROM dbo.syscomments;
    GO
    --

    USE sqlsvr_audit;
    GO
    --
    CREATE TRIGGER trg_auditlogins
    ON dbo.syscomments
    FOR INSERT, UPDATE, DELETE
    AS
    SELECT * INTO auditlogins
    FROM dbo.syslogins;
    GO
    --

    USE sqlsvr_audit;
    GO
    --
    CREATE TRIGGER trg_audit_sysobjects
    ON dbo.sysobjects
    FOR INSERT, UPDATE, DELETE
    AS
    SELECT * INTO auditsysobjects
    FROM dbo.sysobjects;
    GO
    --
    USE sqlsvr_audit;
    GO
    --

    CREATE TRIGGER trg_audit_files
    ON dbo.sysfiles
    FOR INSERT, UPDATE, DELETE
    AS
    SELECT * INTO auditfiles
    FROM dbo.sysfiles;
    GO
    --

    USE sqlsvr_audit;
    GO
    --

    CREATE TRIGGER trg_audit_users
    ON dbo.sysusers
    FOR INSERT, UPDATE, DELETE
    AS
    SELECT * INTO auditusers
    FROM dbo.sysusers;
    GO
    --

    USE sqlsvr_audit;
    GO

    I keep getting these syntax and permissions errors with MS SQL Server 2000:

    Server: Msg 170, Level 15, State 1, Procedure trg_audit_version, Line 7
    Line 7: Incorrect syntax near '@@version'.
    Server: Msg 229, Level 14, State 5, Procedure trg_audit1, Line 6
    CREATE TRIGGER permission denied on object 'syscomments', database 'sqlsvr_audit', owner 'dbo'.
    Server: Msg 229, Level 14, State 5, Procedure trg_auditlogins, Line 6
    CREATE TRIGGER permission denied on object 'syscomments', database 'sqlsvr_audit', owner 'dbo'.
    Server: Msg 229, Level 14, State 5, Procedure trg_audit_sysobjects, Line 6
    CREATE TRIGGER permission denied on object 'sysobjects', database 'sqlsvr_audit', owner 'dbo'.
    Server: Msg 229, Level 14, State 5, Procedure trg_audit_files, Line 7
    CREATE TRIGGER permission denied on object 'sysfiles', database 'sqlsvr_audit', owner 'dbo'.
    Server: Msg 229, Level 14, State 5, Procedure trg_audit_users, Line 7

    Can anyone help me out here and how to fix these problems with my script? Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Here's what BOL says..

    Note Because SQL Server does not support user-defined triggers on system tables, it is recommended that no user-defined triggers be created on system tables.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2003
    Posts
    369

    any workaround to audit changes made to system tables?

    Thanks. Besides using the SQL Profiler trace utility is there a method to custom script in T-SQL changes made to these tables ? Oracle allows one to do so and since I am fairly new to SQL Server would be great if a custom way to do this on a periodic basis for monitor security of the databases. Thanks

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SQL Address that in it's next release with the service broker

    http://www.informit.com/articles/art...27394&seqNum=5

    right now the only thing you can do is to restrict access and do compares of 2 database catalogs.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •