Grant Sp_who2, & who3 proc execution without granting sysadmin access

• Subhash Chandra

  Say Hey Kid

  Points: 692

  More actions

  March 22, 2018 at 10:07 am

  #336743

  Comments posted to this topic are about the item Grant Sp_who2, & who3 proc execution without granting sysadmin access

• jonathan.d.myers

  Old Hand

  Points: 325

  More actions

  March 28, 2018 at 5:59 am

  #1984758

  Would the users still need "View Server State" to access the tables in the view below?
  _______________________
  Create

  View vw_who2
  as
  SELECT
  SPID = er.session_id
  ,DATEDIFF(mi,er.start_time,getdate()) AS [Runtime(m)]
  ,Status = ses.status
  ,[Login] = ses.login_name
  ,Host = ses.host_name
  ,BlkBy = er.blocking_session_id
  ,DBName = DB_Name(er.database_id)
  ,CommandType = er.command
  ,SQLStatement =
  SUBSTRING
  (qt.text,
  er.statement_start_offset/2,
  (CASE WHEN er.statement_end_offset = -1
  THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
  ELSE er.statement_end_offset 
  END - er.statement_start_offset)/2 + 2 )
  ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
  ,CPUTime = er.cpu_time
  ,IOReads = er.logical_reads + er.reads
  ,IOWrites = er.writes
  ,LastWaitType = er.last_wait_type
  ,StartTime = er.start_time
  FROM sys.dm_exec_requests er
  LEFT JOIN sys.dm_exec_sessions ses
  ON ses.session_id = er.session_id
  LEFT JOIN sys.dm_exec_connections con
  ON con.session_id = ses.session_id
  CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
  WHERE er.session_id <> @@SPID 
    AND er.session_id > 50 
  _______________________
  

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  March 29, 2018 at 11:37 am

  #1985019

  jonathan.d.myers - Wednesday, March 28, 2018 5:59 AM

  Would the users still need "View Server State" to access the tables in the view below?
  _______________________
  Create

  View vw_who2
  as
  SELECT
  SPID = er.session_id
  ,DATEDIFF(mi,er.start_time,getdate()) AS [Runtime(m)]
  ,Status = ses.status
  ,[Login] = ses.login_name
  ,Host = ses.host_name
  ,BlkBy = er.blocking_session_id
  ,DBName = DB_Name(er.database_id)
  ,CommandType = er.command
  ,SQLStatement =
  SUBSTRING
  (qt.text,
  er.statement_start_offset/2,
  (CASE WHEN er.statement_end_offset = -1
  THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
  ELSE er.statement_end_offset 
  END - er.statement_start_offset)/2 + 2 )
  ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
  ,CPUTime = er.cpu_time
  ,IOReads = er.logical_reads + er.reads
  ,IOWrites = er.writes
  ,LastWaitType = er.last_wait_type
  ,StartTime = er.start_time
  FROM sys.dm_exec_requests er
  LEFT JOIN sys.dm_exec_sessions ses
  ON ses.session_id = er.session_id
  LEFT JOIN sys.dm_exec_connections con
  ON con.session_id = ses.session_id
  CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
  WHERE er.session_id <> @@SPID 
    AND er.session_id > 50 
  _______________________
  

  Yes.
  FYI.....The older practice of spids > 50 being just user sessions no longer applies. You would want to use is_user_process = 1 from sys.dm_exec_sessions to limit it to user processes.

  Sue

• Subhash Chandra

  Say Hey Kid

  Points: 692

  More actions

  April 25, 2018 at 12:21 am

  #1988145

  Hi jonathan.d.myers & Sue_H, 

  Thanks for your valuable inputs.