Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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. – Aaron Bertrand Feb 12 '13 at 18:08
up vote 14 down vote accepted

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

share|improve this answer
Here is the link to the manual: – a_horse_with_no_name Feb 12 '13 at 18:03
@a_horse_with_no_name Thanks, was looking for that. :) Link added to answer. – Mike Fal Feb 12 '13 at 18:05

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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