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.
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.
Please post your full TRIGGER. I suspect there is something that you are not telling us.
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).
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
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
Have you tried:
ALTER TRIGGER [dbo].[trgAddrUpd]
on [dbo].[tblAddress]
WITH EXECUTE AS OWNER
AFTER UPDATE
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
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.
I am afraid I have no idea. We use that all the time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
I suggest you save this thread with the author's comment https:#a36545958 as the solution.