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...
>
>