2

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

3
  • Ok so basically there is a trigger in a users DB. When someone tries to change a specific user, for security policies, the trigger will disable the user and send an email with some data as who tried to change this user. All my tests work because I test them directly on DB, but at the end there is a software that will work with the DB, so there is a specific application user that uses a specific DB user to perform the logins etc etc This app user doesn't have permissions to use system queries as SELECT client_net_address FROM sys.dm_exec_connections
    – Michael R.
    Feb 5, 2018 at 21:18
  • You can see my trigger on another post: dba.stackexchange.com/questions/197109/…
    – Michael R.
    Feb 5, 2018 at 21:23
  • See the answers in a similar issue: sql server execute as permission errors in trigger and the link from RemusRusanu for a solution: Call a procedure in another database from an activated procedure (in short: don't run a select from another_db.table, call a procedure that access it) Feb 6, 2018 at 9:44

2 Answers 2

3

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');
-- <local machine>

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.

0
1

All you need is to ask yourself what information you need and how non privileged user can retrieve it for himself.

For example, maybe it's enough to you to use host_name() that does not require any particular permission.

Many server level information can be retrieved even without having any server level permission as soon as this information reguards only this current login itself.

....

Otherwise there are only 2 solutions:

  1. You can make your database TRUSTWORTHY, the owner of this db should be changed to a login with AUTHENTICATE SERVER permission, add to your proc WITH EXECUTE AS USER = 'dbo' clause. This is not secure if your database has db_owners that are non sysadmins. They can elevate their permissions to sysadmin. If you have no such users, you can use this method.
  2. You can use module signing. The idea here is to create a login, create a certificate from this login in master, give it VIEW SERVER STATE, export this certificate to your db, sign your proc with this certificate. This method is secured.

You can read about this in detailes here: Packaging Permissions in Stored Procedures.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.