none
Login Detail RRS feed

  • Question

  • Hi
         I need to build a query which can show following detail for those login which are currently accessing sql server objects (table,sp,etc.) through sysadmin permission.
     
              Login Name
              Permission (i.e. Sysadmin)
              Database Name
              SQL Statement 
       
        I don't want use sql profiler, i need t-sql query which can be schduled by job.


    Thanks in advance

    Raj
     

    **Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79
    • Moved by Tom PhillipsModerator Thursday, February 18, 2010 1:49 PM TSQL Question (From:SQL Server Database Engine)
    Thursday, February 18, 2010 9:37 AM

Answers

  • Hi
         I need to build a query which can show following detail for those login which are currently accessing sql server objects (table,sp,etc.) through sysadmin permission.
     
              Login Name
              Permission (i.e. Sysadmin)
              Database Name
              SQL Statement 
       
        I don't want use sql profiler, i need t-sql query which can be schduled by job.


    Thanks in advance

    Raj
     

    **Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79

    You can get everything but Permission from dm_exec_* DMV's and DMF's.  To determine that the account is a sysadmin just do a is_svrrolemember check on it:

    select *
    from sys.dm_exec_sessions as s
    left join sys.dm_exec_requests as r on s.session_id = r.session_id
    cross apply sys.dm_exec_sql_text(sql_handle)
    where IS_SRVROLEMEMBER('sysadmin', s.login_name) = 1

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by Bobby_79 Friday, February 19, 2010 9:31 AM
    Thursday, February 18, 2010 12:28 PM
    Moderator