What is the minimum lever of permission I need to use these views

Question

What is the minimum lever of permission I need to use these views

mw_sql_developer

SSCoach

Points: 19445
More actions
August 2, 2016 at 9:34 am

#312157

Hello
I want to run the following query. But I get the error message ( See attached )
I am a SQL developer and don't need DBA rights.
What is the minimum level of permissions/rights I need so that I can see the query output ?
SELECT
R.SESSION_ID,
R.REQUEST_ID AS SESSION_REQUEST_ID,
R.STATUS,
S.HOST_NAME,
C.CLIENT_NET_ADDRESS,
CASE WHEN S.LOGIN_NAME = S.ORIGINAL_LOGIN_NAME THEN S.LOGIN_NAME ELSE S.LOGIN_NAME + ' (' + S.ORIGINAL_LOGIN_NAME + ')' END AS LOGIN_NAME,
S.PROGRAM_NAME,
DB_NAME(R.DATABASE_ID) AS DATABASE_NAME,
R.COMMAND,
ST.TEXT AS QUERY_TEXT,
QP.QUERY_PLAN AS XML_QUERY_PLAN,
R.WAIT_TYPE AS CURRENT_WAIT_TYPE,
R.LAST_WAIT_TYPE,
R.BLOCKING_SESSION_ID,
R.ROW_COUNT,
R.GRANTED_QUERY_MEMORY,
R.OPEN_TRANSACTION_COUNT,
R.USER_ID,
R.PERCENT_COMPLETE,
CASE R.TRANSACTION_ISOLATION_LEVEL
WHEN 0 THEN 'UNSPECIFIED'
WHEN 1 THEN 'READUNCOMITTED'
WHEN 2 THEN 'READCOMMITTED'
WHEN 3 THEN 'REPEATABLE'
WHEN 4 THEN 'SERIALIZABLE'
WHEN 5 THEN 'SNAPSHOT'
ELSE CAST(R.TRANSACTION_ISOLATION_LEVEL AS VARCHAR(32))
END AS TRANSACTION_ISOLATION_LEVEL_NAME
FROM
SYS.DM_EXEC_REQUESTS R
LEFT OUTER JOIN SYS.DM_EXEC_SESSIONS S ON S.SESSION_ID = R.SESSION_ID
LEFT OUTER JOIN SYS.DM_EXEC_CONNECTIONS C ON C.CONNECTION_ID = R.CONNECTION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) ST
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(R.PLAN_HANDLE) QP
WHERE
R.STATUS NOT IN ('BACKGROUND','SLEEPING')

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Go to the topic in Books Online for each of the views and functions you're using. There'll be a Permissions section, which will tell you what permissions you need to execute it. If the permissions aren't all the same, you'll need the highest in order to run the whole query.

John