locked
BUG or what? sys.dm_exec_connections RRS feed

  • Question

  • Hi

    Im trying to Identify client IP address using sys.dm_exec_connections inside a trigger but with no sucess. SQL Server returns the message:

    Msg 297, Level 16, State 1, Procedure TR_XXXXXXX, Line 52
    The user does not have permission to perform this action.

    Scenario:

    I have 2 users/login: one is db_owner and the other (audit) owns schema audit that owns  my auditing tables. When the trigger is fired, i change the context to audit login/user but it not works.

    It is not a permission problem cause audit login has  VIEW SERVER STATE permission and outside the trigger, i can query sys.dm_exec_connections with no problem.

    the problem only occurs when I change the user context

    Thursday, August 8, 2013 5:59 PM

Answers

  • This is NOT a bug but a behavior by design.

    From the details you provided above, I was able to reproduce the issue using the below script

    use master
    GRANT VIEW SERVER STATE TO [TestServerState]
    
    CREATE TRIGGER TestTrig ON Tab1 
    	AFTER INSERT
    AS 
    
    BEGIN
    EXECUTE AS USER = 'TestServerState'--This has only view server state permissions
    SELECT * FROM sys.dm_exec_connections
    END
    GO
    
    --Running this as a sysadmin (does not matter)
    INSERT INTO Tab1 values (1,'a')

    and got the below error:- 

    Msg 297, Level 16, State 1, Procedure TestTrig, Line 7
    The user does not have permission to perform this action.

    This is expected since you are changing the user context. When you change the user context, the scope of the impersonation is ONLY within the database. Though the user has "VIEW SERVER STATE" permissions which is sufficient for querying the DMV, it does not have any importance when you are performing impersonation.

    This is documented in http://technet.microsoft.com/en-us/library/ms188304(v=sql.105).aspx.

    One workaround this is to set the TRUSTWORTHY property of the database to ON.

    ALTER DATABASE DB1 SET TRUSTWORTHY ON


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Sunday, August 11, 2013 6:29 PM
  • This is NOT a bug but a behavior by design.

    ...

    This is expected since you are changing the user context. When you change the user context, the scope of the impersonation is ONLY within the database. Though the user has "VIEW SERVER STATE" permissions which is sufficient for querying the DMV, it does not have any importance when you are performing impersonation.

    This is documented in http://technet.microsoft.com/en-us/library/ms188304(v=sql.105).aspx.

    One workaround this is to set the TRUSTWORTHY property of the database to ON.

    ALTER DATABASE DB1 SET TRUSTWORTHY ON

    Ah, right, of course. I should have thought about it, since we just had that issue in this thread: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/79391dd7-9afb-4bfb-aa90-1fc1cc82b305/impersonation-on-server-level-doesnt-work

    There you can find a different solution: EXECUTE AS LOGIN

    Thrustworthy works, if the DB is owned by sysadmin. And it opens up much more than this Trigger.. so I would usually recommend against it, unless you know exactly how the security-borders are drawn in your database and who might end up running unsafe code under dbo.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Sunday, August 11, 2013 6:37 PM

All replies

  • This should work, unless some permissions for some actions have been forgotten.

    Could you share the code for the user's permissions set up as well as what the trigger does when and where it is located at?


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Thursday, August 8, 2013 6:03 PM
  • What version you are using, I cannot reproduce this behavior on SQL Server 2012 SP1


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Friday, August 9, 2013 12:44 PM
  • This is NOT a bug but a behavior by design.

    From the details you provided above, I was able to reproduce the issue using the below script

    use master
    GRANT VIEW SERVER STATE TO [TestServerState]
    
    CREATE TRIGGER TestTrig ON Tab1 
    	AFTER INSERT
    AS 
    
    BEGIN
    EXECUTE AS USER = 'TestServerState'--This has only view server state permissions
    SELECT * FROM sys.dm_exec_connections
    END
    GO
    
    --Running this as a sysadmin (does not matter)
    INSERT INTO Tab1 values (1,'a')

    and got the below error:- 

    Msg 297, Level 16, State 1, Procedure TestTrig, Line 7
    The user does not have permission to perform this action.

    This is expected since you are changing the user context. When you change the user context, the scope of the impersonation is ONLY within the database. Though the user has "VIEW SERVER STATE" permissions which is sufficient for querying the DMV, it does not have any importance when you are performing impersonation.

    This is documented in http://technet.microsoft.com/en-us/library/ms188304(v=sql.105).aspx.

    One workaround this is to set the TRUSTWORTHY property of the database to ON.

    ALTER DATABASE DB1 SET TRUSTWORTHY ON


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Sunday, August 11, 2013 6:29 PM
  • This is NOT a bug but a behavior by design.

    ...

    This is expected since you are changing the user context. When you change the user context, the scope of the impersonation is ONLY within the database. Though the user has "VIEW SERVER STATE" permissions which is sufficient for querying the DMV, it does not have any importance when you are performing impersonation.

    This is documented in http://technet.microsoft.com/en-us/library/ms188304(v=sql.105).aspx.

    One workaround this is to set the TRUSTWORTHY property of the database to ON.

    ALTER DATABASE DB1 SET TRUSTWORTHY ON

    Ah, right, of course. I should have thought about it, since we just had that issue in this thread: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/79391dd7-9afb-4bfb-aa90-1fc1cc82b305/impersonation-on-server-level-doesnt-work

    There you can find a different solution: EXECUTE AS LOGIN

    Thrustworthy works, if the DB is owned by sysadmin. And it opens up much more than this Trigger.. so I would usually recommend against it, unless you know exactly how the security-borders are drawn in your database and who might end up running unsafe code under dbo.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Sunday, August 11, 2013 6:37 PM