Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer 1

up vote 7 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:

GRANT VIEW SERVER STATE TO marie;
share|improve this answer
    
Here is the link to the manual: msdn.microsoft.com/en-us/library/ms188754%28v=sql.105%29.aspx –  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
add comment

Your Answer

 
discard

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.