Microsoft KB Archive/47270

= INFO: SQL Access Permissions and Trigger Execution =

Article ID: 47270

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q47270



SUMMARY
The following information discusses the topic of access permissions and trigger execution.



MORE INFORMATION
If an operation on an object causes an implicit operation on another object, and both objects have the same owner, it is assumed that all privileges granted for the explicit operation also apply to the implicit operation. If the objects have different owners, it is assumed that the different owners may have different ideas about who should be granted access to what.

If user U1 owns table T1, and that table has a trigger that accesses table T2 owned by user U2, then a third user U3 must have explicit permission to access tables T1 and T2. If user U1 had also owned T2, then user U3 would require only explicit permission to use T1.

Triggers should not provide a way for someone other than the owner of an object to determine who has access.

In a production environment, all objects should be owned by the DBA and this is not a problem. In a free-for-all environment, with many users creating objects and triggers, this is the only way to provide security.

Beginning with SQLServer 6.0, more information on this topic can be found within Books Online under the topic : &quot;Ownership Chains&quot;.

Additional query words: Transact-SQL permissions

Keywords: kbinfo kbusage KB47270

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.