Link to home
Start Free TrialLog in
Avatar of baronga
baronga

asked on

EXECUTE AS Permissions To Query system tables requireing View Server State

I'm trying to put the following query into a trigger (after declaring the variables):

  SELECT @IPAddress = c.client_net_address
      ,@Port = c.local_tcp_port
      ,@Host = s.host_name
      ,@Program = s.program_name
      ,@Login      = s.login_name
  FROM sys.dm_exec_sessions s JOIN sys.dm_exec_connections c ON
      s.session_id = c.session_id
  WHERE s.session_id = @@spid

When I log into the server as 'sa' and run it from a New Query window in SQL 2008, it runs fine; however, if I try to hit it from a client (.NET web) application (which comes in as a user with limited permissions), I get an error stating that I don't have adequate permissions.

If I try to wrap the query in the following:
    EXECUTE AS USER = 'dbo'
    REVERT

I get Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

If I try altering the trigger to WITH EXECUTE AS SELF, I get the same results.

In both cases, this happens even when I run it in a New Query window, when I AM LOGGED IN AS 'sa'.  In this case the client application simply times out.

I've also tried creating a separate function WITH EXECUTE AS OWNER to return just the IP Address from sys.dm_exec_connections, and got the same Msg 15517 (above).

I would RATHER NOT try to create a login to the server with "VIEW SERVER STATE" permissions, as that has to be granted on a login, and can't be granted on a user created "WITHOUT LOGIN".

Does anyone know how to get the EXECUTE AS functionality working/what other settings I might need to look at to permit this?

Thank you for any help you can offer.
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Please post your full TRIGGER.  I suspect there is something that you are not telling us.
Avatar of baronga
baronga

ASKER

Here's the code for the full trigger.  You will see a section that queries registry tables.  

     IF LTrim(RTrim(@IPAddress)) = '<local machine>'
     BEGIN
          ...
     END

This part works fine in the new Query Window (logged in as 'sa'), and is successfully bypassed in the client application.  Additionally, I commented that portion out, while trying the various "EXECUTE AS" impersonation methods, so that is not what's throwing it off.

When I tried EXECUTE AS USER = 'dbo' / REVERT, I removed the WITH EXECUTE AS SELF from the beginning, and placed the EXECUTE AS USER = 'dbo' directly above the select statement (line 11, after variable declarations), and put REVERT directly after the same select statement (line 19).



ALTER TRIGGER [dbo].[trgAddrUpd]
	on [dbo].[tblAddress]
   WITH EXECUTE AS SELF
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON

	DECLARE @Port int = NULL, @Login VarChar(128) = NULL, @IPAddress VarChar(48) = NULL, @Host VarChar(128) = NULL, @Program varchar(128) = NULL
	DECLARE @SAMUser VarChar(20) = NULL, @LastUser VarChar(20) = NULL
	SELECT @IPAddress = c.client_net_address
		,@Port = c.local_tcp_port
		,@Host = s.host_name
		,@Program = s.program_name
		,@Login	= s.login_name
	from sys.dm_exec_sessions s 
	JOIN sys.dm_exec_connections c ON
		s.session_id = c.session_id
	WHERE s.session_id = @@spid

	IF LTrim(RTrim(@IPAddress)) = '<local machine>'
	BEGIN
		DECLARE @value VARCHAR(20)
		DECLARE @key VARCHAR(100)

		--DECLARE @value VARCHAR(20)
		EXEC master..xp_regread
		   @rootkey = 'HKEY_LOCAL_MACHINE',
		   @key = 'Software\Microsoft\Windows\CurrentVersion\Authentication\LogonUI',
		   @value_name = 'LastLoggedOnSAMUser',
		   @SAMUser = @SAMUser OUTPUT

		EXEC master..xp_regread
		   @rootkey = 'HKEY_LOCAL_MACHINE',
		   @key = 'Software\Microsoft\Windows\CurrentVersion\Authentication\LogonUI',
		   @value_name = 'LastLoggedOnUser',
		   @LastUser = @LastUser OUTPUT
	END

	-- Insert statements for trigger here
	INSERT INTO tblAddressHist
	(	AddressID
		,Address1
		,Address2
		,City
		,Zip
		,IPAddress
		,TransDate
		,TransType
		,Host
		,SQLLogin
		,Program
		,SAMUser
		,LastUser
		,PortUsed
	)

	SELECT
		AddressID
		,Address1
		,Address2
		,City
		,Zip
		,@IPAddress
		,GetDate()
		,'U'
		,@Host
		,@Login
		,@Program
		,@SAMUser
		,@LastUser
		,@Port
	FROM INSERTED

END

Open in new window

So it looks like the reason you want to impersonate another user is because you want to use the system VIEW sys.dm_exec_session and sys.dm_exec_connections as well as the undocument function xp_regread.

Have you tried:
ALTER TRIGGER [dbo].[trgAddrUpd]
      on [dbo].[tblAddress]
   WITH EXECUTE AS OWNER
   AFTER UPDATE
Avatar of baronga

ASKER

Yes, I had tried EXECUTE AS OWNER, and like EXECUTE AS SELF, it generates the same error:

Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission
>>Yes, I had tried EXECUTE AS OWNER, and like EXECUTE AS SELF, it generates the same error:<<
I am afraid I have no idea.  We use that all the time.

ASKER CERTIFIED SOLUTION
Avatar of baronga
baronga

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I suggest you save this thread with the author's comment https:#a36545958 as the solution.
Let's try that again:
I suggest you save this thread with the author's comment https:#a36545958 as the solution.