q; user running trigger

Is it possible fire an insert trigger in the context of a different user than
the user that does the insert.
In SQL2005, you can specify a WITH EXECUTE AS clause on the trigger create
statement so security context other than the current user. However, in all
SQL Server versions, permission checking on objects referenced in the
trigger are bypassed as long as the ownership chain is unbroken. This means
that you only need to be concerned about the trigger security context if you
access objects owned by principals, use dynamic SQL or access objects
external to the current database.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6B775494-846A-4875-A9C0-6AC7ECDFD9E1@.microsoft.com...
> Is it possible fire an insert trigger in the context of a different user
> than
> the user that does the insert.
|||We are using SQL2000. User insert table in one database T1 and trigger insert
the record into another database T2. In this scenario could you please be
more clear if I could use any user in T2 to be able to do this insertion.
Users in T1 should not be able to insert anything into T2.
"Dan Guzman" wrote:

> In SQL2005, you can specify a WITH EXECUTE AS clause on the trigger create
> statement so security context other than the current user. However, in all
> SQL Server versions, permission checking on objects referenced in the
> trigger are bypassed as long as the ownership chain is unbroken. This means
> that you only need to be concerned about the trigger security context if you
> access objects owned by principals, use dynamic SQL or access objects
> external to the current database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:6B775494-846A-4875-A9C0-6AC7ECDFD9E1@.microsoft.com...
>
|||You might consider using cross-database ownership chaining to address your
security requirement. Assuming all objects are owned by dbo, you can do
this as follows:
1) Make sure both databases are owned by the same login. Execute
sp_changedbowner if necessary.
2) Make sure users are added to both databases so that they have a security
context. However, those users do not need object permissions in the second
database. Alternatively, you can enable the guest user in the second
database.
3) Enable the cross-database chaining ('db chaining' database option) for
both databases.
Note that you should understand the security implications of cross-database
chaining as described in the Books Online. Cross-database chaining should
be enabled in an sa-owned database when only sysadmin role members can
create dbo-owned objects in those databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6EBDA8BD-9B96-4503-A1C8-95E8D872E34F@.microsoft.com...[vbcol=seagreen]
> We are using SQL2000. User insert table in one database T1 and trigger
> insert
> the record into another database T2. In this scenario could you please be
> more clear if I could use any user in T2 to be able to do this insertion.
> Users in T1 should not be able to insert anything into T2.
> "Dan Guzman" wrote: