The "SupportRole" Role most likely gets the permission error when the trigger tries to
audit.Assesments due to:
Dynamic SQL is being used for the
audit schema has an owner that is not
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
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
principal_id) as the owner of the executing object, and
- implicit grants are only:
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.
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).
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