EXECUTE AS USER = 'dbo' - The user does not have permission to perform this action
No, using EXECUTE AS 'dbo' in the CREATE TRIGGER statement and setting the Database to TRUSTWORTHY ON is not an option. I mean, it would technically work, but it's a completely unnecessary security risk (which is the case 99.9% of the time when wondering if you should enable TRUSTWORTHY).

Yes, you could use a Certificate to sign the Trigger, then copy the Certificate to [master] and create a Login from it and then grant that Certificate-based Login the VIEW SERVER STATE Server-level permission. That would also technically work. However, while I am a huge fan of Module Signing, I prefer simplicity when / where available (as long as it is not a security risk or unmaintainable). Fortunately for this situation (i.e. getting connection info for the current Session / @@SPID) there is a built-in function that has what you need: CONNECTIONPROPERTY:

SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID;
/*
Msg 297, Level 16, State 1, Line XXXXX
The user does not have permission to perform this action.
*/

SELECT CONNECTIONPROPERTY('client_net_address');
-- &lt;local machine&gt;


So, in your Trigger, replace your current DECLARE @source line with the following:

DECLARE @NetAddress VARCHAR(48) =
                     CONVERT(VARCHAR(48), CONNECTIONPROPERTY('client_net_address'));


Please note the change in datatype from what you originally had. The documentation states that the base type is VARCHAR(48), so it's best to go with that.