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!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.|||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|||SQL Address that in it's next release with the service broker

http://www.informit.com/articles/article.asp?p=327394&seqNum=5

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

permissions needed for trigger

Using SS2000 SP4. We're using a .NET application. What permissions should be
needed for a user to fire a trigger. Ideally, I wanted the user (userWill) t
o
only have execute permissions on the stored procedures and select permission
s
on the views. But all views, stored procedures and triggers are qualified
with "dbo".
But when I try to update a table and the trigger fires I get these errors:
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'tblFranchiseContacts', database
'SMCLMS_Dan', owner 'dbo'.
Server: Msg 229, Level 14, State 1, Line 1
UPDATE permission denied on object 'tblFranchiseContacts', database
'SMCLMS_Dan', owner 'dbo'.
If I run exec sp_helpdb 'smclms_dan' I get archer\dbober as the owner.
How do I get around this?
Thanks,
--
Dan D.Hi,
You do not need to specify permissions on triggers. Triggers are executed on
UPDATE, INSERT and DELETE statements. So, in order to execute these triggers
users need to have these permissions on the tables holding the triggers non
on the trigger themselves.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Dan D." wrote:

> Using SS2000 SP4. We're using a .NET application. What permissions should
be
> needed for a user to fire a trigger. Ideally, I wanted the user (userWill)
to
> only have execute permissions on the stored procedures and select permissi
ons
> on the views. But all views, stored procedures and triggers are qualified
> with "dbo".
> But when I try to update a table and the trigger fires I get these errors:
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'tblFranchiseContacts', database
> 'SMCLMS_Dan', owner 'dbo'.
> Server: Msg 229, Level 14, State 1, Line 1
> UPDATE permission denied on object 'tblFranchiseContacts', database
> 'SMCLMS_Dan', owner 'dbo'.
> If I run exec sp_helpdb 'smclms_dan' I get archer\dbober as the owner.
> How do I get around this?
> Thanks,
> --
> Dan D.|||Dan,
If the owner of the table and the owner of the sp or view are the same then
the end-user accessing the sp or view does not need to have permissions on
the underlying tables. This provides a security abstraction layer to the
underlying objects. However, if the owners are not the same then you have a
broken ownership chain in which case permissions are required. There are no
execute trigger permissions.
For more information on this, see the security chapter I wrote a while back
in the SQL Server 2000 Operations Guide at:
http://www.microsoft.com/technet/pr...in/sqlops3.mspx
HTH
Jerry
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:056F44C1-5885-423B-A694-64200ECF196B@.microsoft.com...
> Using SS2000 SP4. We're using a .NET application. What permissions should
> be
> needed for a user to fire a trigger. Ideally, I wanted the user (userWill)
> to
> only have execute permissions on the stored procedures and select
> permissions
> on the views. But all views, stored procedures and triggers are qualified
> with "dbo".
> But when I try to update a table and the trigger fires I get these errors:
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'tblFranchiseContacts', database
> 'SMCLMS_Dan', owner 'dbo'.
> Server: Msg 229, Level 14, State 1, Line 1
> UPDATE permission denied on object 'tblFranchiseContacts', database
> 'SMCLMS_Dan', owner 'dbo'.
> If I run exec sp_helpdb 'smclms_dan' I get archer\dbober as the owner.
> How do I get around this?
> Thanks,
> --
> Dan D.|||You need only to set permissions on the sprocs themselves. If the sprocs use
dynamic sql, then the tables need permissions set, too
Jeff
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:056F44C1-5885-423B-A694-64200ECF196B@.microsoft.com...
> Using SS2000 SP4. We're using a .NET application. What permissions should
> be
> needed for a user to fire a trigger. Ideally, I wanted the user (userWill)
> to
> only have execute permissions on the stored procedures and select
> permissions
> on the views. But all views, stored procedures and triggers are qualified
> with "dbo".
> But when I try to update a table and the trigger fires I get these errors:
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'tblFranchiseContacts', database
> 'SMCLMS_Dan', owner 'dbo'.
> Server: Msg 229, Level 14, State 1, Line 1
> UPDATE permission denied on object 'tblFranchiseContacts', database
> 'SMCLMS_Dan', owner 'dbo'.
> If I run exec sp_helpdb 'smclms_dan' I get archer\dbober as the owner.
> How do I get around this?
> Thanks,
> --
> Dan D.|||Thanks to you all for your replies and help.
--
Dan D.
"Jerry Spivey" wrote:

> Dan,
> If the owner of the table and the owner of the sp or view are the same the
n
> the end-user accessing the sp or view does not need to have permissions on
> the underlying tables. This provides a security abstraction layer to the
> underlying objects. However, if the owners are not the same then you have
a
> broken ownership chain in which case permissions are required. There are
no
> execute trigger permissions.
> For more information on this, see the security chapter I wrote a while bac
k
> in the SQL Server 2000 Operations Guide at:
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops3.mspx[/url
]
> HTH
> Jerry
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:056F44C1-5885-423B-A694-64200ECF196B@.microsoft.com...
>
>