14

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?

  • 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
25

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;
  • 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
  • This isn't supported in Azure SQL db - how do I achieve the equivalent here? – tommed Sep 30 '16 at 11:35
  • 1
    Try GRANT VIEW DATABASE STATE. – Mike Fal Sep 30 '16 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 Jul 4 '17 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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