EXECUTE AS USER = 'dbo' - The user does not have permission to perform this action I created a trigger that now works fine as result, but there is 1 line where I run a query:

DECLARE @source as nvarchar(30) = (SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID);


The user that will trigger this, has no permission to execute something like

EXECUTE AS USER = 'dbo' ;   


I receive the error:

The user does not have permission to perform this action.


How can I achieve this?

The whole trigger code can be seen in this question:
UPDATE Trigger with condition
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.