Grant Sp_who2, & who3 proc execution without granting sysadmin access

Question

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jonathan.d.myers Old Hand Points: 325 More actions · Answer 1

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 · Answer 2

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 · Answer 3

Hi jonathan.d.myers & Sue_H,

Thanks for your valuable inputs.