Is it possible to grant permission to a trigger that requires permission user does not have?

Question

In the past I used a db trigger to log all schema changes. It was glorious. However, it required anyone who needed to modify schema to have certain permissions. I believe one that's needed is VIEW SERVER STATE. Can't recall for certain, but it's not really important which one(s) are needed for this post.

The question is, what if there are users who can change schema but who should not have that permission? This completely breaks my tool because the trigger will just cause them to get this error. I could wrap it in a try catch, but then that obviously defeats the whole purpose if it doesn't log the change.

Is there a way to allow this trigger to run for everyone even though I can't give everyone all required permissions? I'm guessing the answer is no because I don't know how you could fire a trigger under some sort of impersonation and I can't think of any other solution. But I'm hopeful you guys know something I don't!

Here is the trigger:

CREATE TRIGGER [LogSchemaEvent]
    ON DATABASE
    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
        CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
        CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, 
        CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
        CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
        CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA,
        CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
        CREATE_TYPE, DROP_TYPE
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @EventData XML = EVENTDATA()

    DECLARE @ip VARCHAR(32) = (
        SELECT client_net_address
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID)
 
    INSERT SchemaEvents (
        EventType,
        EventDDL,
        EventXML,
        DatabaseName,
        SchemaName,
        ObjectName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName)
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData,
        DB_NAME(),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END

Answer 1

Elevated permissions can be conferred to a module (procedure, function, trigger) by signing it with a certificate associated with a user with the needed permissions. This works well for schema-scoped objects but not with DDL triggers because these cannot be signed directly.

You can, however, create a schema-scoped proc with the needed functionality, sign it with a certificate, and call it from your DDL trigger. The only downside with this approach is the database must be TRUSTWORTHY. Resorting to TRUSTWORTHY is something I generally recommend one avoid but may be acceptable in your case of a development database.

Below is an example script.

USE YourDatabase;
GO

CREATE OR ALTER PROCEDURE dbo.LogDDLEvent
    @EventData xml
AS 
INSERT SchemaEvents (
    EventType,
    EventDDL,
    EventXML,
    DatabaseName,
    SchemaName,
    ObjectName,
    HostName,
    IPAddress,
    ProgramName,
    LoginName)
SELECT
    @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
    @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
    @EventData,
    DB_NAME(),
    @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
    @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
    HOST_NAME(),
    (SELECT TOP 1 client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID),
    PROGRAM_NAME(),
    SUSER_SNAME();
GO
GRANT EXECUTE ON dbo.LogDDLEvent TO public;
GO

CREATE OR ALTER TRIGGER [LogSchemaEvent]
    ON DATABASE

    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
        CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
        CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, 
        CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
        CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
        CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA,
        CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
        CREATE_TYPE, DROP_TYPE
    
AS
SET NOCOUNT ON;
DECLARE @EventData XML = EVENTDATA();
EXECUTE dbo.LogDDLEvent @EventData = @EventData;
GO

USE master
GO

-- Create certificate in master.
CREATE CERTIFICATE LogSchemaEventCertificate
   ENCRYPTION BY PASSWORD = 'ephemoral password'
   WITH SUBJECT = 'For view server state';
CREATE LOGIN LogSchemaEventCertificateUser FROM CERTIFICATE LogSchemaEventCertificate;
GRANT VIEW SERVER STATE TO LogSchemaEventCertificateUser;

-- Copy cert to user database
DECLARE @cert_id int = cert_id('LogSchemaEventCertificate')
DECLARE @public_key  varbinary(MAX) = certencoded(@cert_id),
        @private_key varbinary(MAX) =
           certprivatekey(@cert_id,
              'ephemoral password',
              'ephemoral password');

SELECT @cert_id, @public_key, @private_key; --these values should be non-null

DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE LogSchemaEventCertificate
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          convert(varchar(MAX), @private_key, 1) + ',
          DECRYPTION BY PASSWORD = ''ephemoral password'',
          ENCRYPTION BY PASSWORD = ''ephemoral password'')';

EXEC YourDatabase.sys.sp_executesql @sql;
ALTER CERTIFICATE LogSchemaEventCertificate REMOVE PRIVATE KEY;
ALTER DATABASE YourDatabase SET TRUSTWORTHY ON;
GO

USE YourDatabase;
ADD SIGNATURE TO dbo.LogDDLEvent BY CERTIFICATE LogSchemaEventCertificate WITH PASSWORD = 'ephemoral password';
ALTER CERTIFICATE LogSchemaEventCertificate REMOVE PRIVATE KEY;
GO