I have a database Microsoft SQL Server 2008 r2. A user called marie is defined, who can access tables on the main database; this works well. Now, I would like marie to be able to perform this query:

SELECT resource_type,spid,login_time,status,hostname,program_name,nt_domain,nt_username,loginame
  FROM sys.dm_tran_locks dl
  JOIN sys.sysprocesses sp on dl.request_session_id = sp.spid

The tables involved are master tables; how do I give permission to marie to read from them? I already tried to execute the following as dbo:

GRANT ALL on sys.dm_tran_locks TO marie
GRANT ALL on sys.sysprocesses TO marie

Still, when marie tries to perform the query above, the error is:

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

What am I doing wrong?

  • 1
    Also you should be using sys.dm_exec_requests, sys.dm_exec_sessions, etc. sysprocesses is a backward compatibility view and will be removed from the product at some point. Commented Feb 12, 2013 at 18:08

1 Answer 1


System views require a slightly elevated state to view, since they are instance wide. You'll want to GRANT VIEW SERVER STATE for this:

  • Here is the link to the manual: msdn.microsoft.com/en-us/library/ms188754%28v=sql.105%29.aspx
    – user1822
    Commented Feb 12, 2013 at 18:03
  • @a_horse_with_no_name Thanks, was looking for that. :) Link added to answer.
    – Mike Fal
    Commented Feb 12, 2013 at 18:05
  • This isn't supported in Azure SQL db - how do I achieve the equivalent here?
    – tommed
    Commented Sep 30, 2016 at 11:35
  • 1
    – Mike Fal
    Commented Sep 30, 2016 at 18:40
  • Some tables under sys can indeed be selected from with only select permissions of a single database, such as sys.indexes.
    – John
    Commented Jul 4, 2017 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.