Solved

EXECUTE AS Permissions To Query system tables requireing View Server State

Posted on 2011-09-12
10
108 Views
Last Modified: 2016-06-17
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.
0
Comment
Question by:baronga
  • 5
  • 3
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36526694
Please post your full TRIGGER.  I suspect there is something that you are not telling us.
0
 

Author Comment

by:baronga
ID: 36528873
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

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36530228
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
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:baronga
ID: 36531622
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36533440
>>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.

0
 

Accepted Solution

by:
baronga earned 0 total points
ID: 36545958
I found a workaround--

It looks like "EXECUTE AS " works on other procedures, but it just doesn't like those sys.dm_exec tables.  After more research, I found built-in functions to get the same information, and used them, instead (and they don't require EXECUTE AS impersonation, at all).  Code is attached:

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

	DECLARE @SAMUser VarChar(20) = NULL, @LastUser VarChar(20) = NULL
	DECLARE @IPAddress VarChar(48) = convert(VarChar(48),CONNECTIONPROPERTY('client_net_address'))

	IF LTrim(RTrim(@IPAddress)) IS NULL		-- = '<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_NAME()
		,ORIGINAL_LOGIN()
		,APP_NAME()
		,@SAMUser
		,@LastUser
		,convert(int, CONNECTIONPROPERTY('local_tcp_port'))
	FROM INSERTED

END

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 41657559
I suggest you save this thread with the author's comment https:#a36545958 as the solution.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 41657561
Let's try that again:
I suggest you save this thread with the author's comment https:#a36545958 as the solution.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now