Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JekaCh
Starting Member
3 Posts |
 Posted - 2008-06-30 : 09:52:42
|
| Good day, all. I have some problem with permissions, couldn't you help me. I want to track changes in my table, for this I wrote a trigger which gathers all possible information about user who was making an update (SUSER_SNAME(),HOST_NAME(),APP_NAME(), @UserAddress) and stores it in table. But I have problem with one field - client IP address. I get it using such expression SET @UserAddress=(SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id=@@SPID), when I update table from my account (sysadmin) everything is ok, but when I'am trying to update table from another account (with permissions on updating table only) I earn message about insufficience of rigts because of @UserAddress. How can I avoid such situation? |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2008-06-30 : 19:14:15
|
| From: BOL topic "sys.dm_exec_connections"Requires VIEW SERVER STATE permission on the server.Does your "other" user have this permission?=======================================We should try to be the parents of our future rather than the offspring of our past. -Miguel de Unamuno |
 |
|
|
JekaCh
Starting Member
3 Posts |
Posted - 2008-07-01 : 03:28:57
|
| I guess no because user belongs to public server role and if trigger starts it starts from his login? Are there any decisions? I dont want to give simple users some advaced permissions. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2008-07-01 : 13:17:08
|
| I can suggest that you (as sysadmin?) put the logic which gets the IP address into a function or stored procedure and then GRANT execution rights to your "other" user (public?). This should allow access to the one piece of data that you care about without opening up your security needlessly.=======================================Some people walk in the rain, others just get wet. -Roger Miller, musician (1936-1992) |
|
|
|
JekaCh
Starting Member
3 Posts |
Posted - 2008-07-02 : 01:43:46
|
| Thank you for your answer but id didn't help :(. I've created a TestUser and TestRole and function [dbo].[getIP](CREATE FUNCTION [dbo].[GetIP] (@s_id bigint)RETURNS varchar(50)AS BEGIN DECLARE @UserAddress as varchar(50) SET @UserAddress=(SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id=@@SPID) RETURN @UserAddress END) , made TestUser a member of TestRole and gave TestRole execution rights for the function [dbo].[getIP]. But when I try to execute query Select MyDB.dbo.GetIP(@@SPID) I still earn message that user do not have rights for performing this action. What have I made wrong? |
|
|
|
|
|
|
Trigger permissions problem