EXPLANATION
The "SupportRole" Role most likely gets the permission error when the trigger tries to INSERT
into audit.Assesments
due to:
Dynamic SQL is being used for the INSERT
statement
and/or
The audit
schema has an owner that is not dbo
Both of those scenarios break the "ownership chaining" that you were expecting would allow this to work, which brings us to:
I had thought triggers were more like stored procedures, where if they are made they have the rights to do whatever they are doing
Triggers are essentially stored procedures that just happen to get automatically executed based on an event, and have access to some of the context of that event (i.e. the inserted
and deleted
virtual tables). However, it's not true, not even for stored procedures, that they have "the rights to do whatever they are doing". That is a misunderstanding of "ownership chaining".
Ownership chaining is a convenience that grants some permissions on objects that are referenced within an object. These implicit grants are limited as follows:
- objects need to be owned by the same SID (not user
name
or principal_id
) as the owner of the executing object, and
- implicit grants are only:
INSERT
, UPDATE
, DELETE
, SELECT
, and EXECUTE
.
Meaning, even if a table referenced in a stored procedure has the same owner (i.e. same SID), there is still no implied permission allowing for either TRUNCATE TABLE
or SET IDENTITY INSERT
.
SOLUTION
Don't
First, I generally recommend against using WITH EXECUTE AS
. The problem is that it completely swaps out the caller's permissions for the specified user's permissions, which might be way more than you are wanting. Also, those new permissions, unless explicitly reverted or changed to another user's, are in effect for all nested calls, no matter how broad or deep. This can also lead to unwanted extending of permissions beyond the original goal.
Second, unless there's a very specific reason for doing so (and there are very few), I also recommend against using OWNER
as the EXECUTE AS
user. The problem here is that object ownership is volatile. Ownership can change in several ways:
- change the ownership of the object itself.
- change the ownership of the schema containing the object (assuming the object does not have an owner explicitly defined)
- move the object to another schema that has a different owner (assuming the object does not have an owner explicitly defined)
If it's absolutely necessary to use WITH EXECUTE AS
, and you plan to specify OWNER
because the object is in the dbo
schema and you need dbo
-level permissions, then it would be more stable to specify WITH EXECUTE AS N'dbo'
.
To be clear: I'm not saying that using WITH EXECUTE AS
is evil, should never be used, and you're horrible if you recommend it and/or use it. I just have a (very) strong preference for the solution mentioned below for the reasons stated here (above and below).
Do
I recommend Module Signing for a variety of reasons, including:
- you simply add the permissions you want to the caller's permissions.
- the permissions only cover the executing module, not nested module references.
The general structure of how to implement this for your situation is:
- Create a certificate in the database containing these objects.
- Create a user from that certificate.
- Grant the certificate-based user whatever permissions are necessary.
- If the object (trigger, stored procedure, etc) already has a
WITH EXECUTE AS
clause, ALTER
the object to remove that clause (this must be done before signing as any ALTER
object statement will automatically drop any signatures on the object being altered).
- Sign the module(s) that need the permission(s) with the
ADD SIGNATURE
statement.
For more information, please visit my site: Module Signing Info