16

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?

carlo.borreo
  • 1,477
  • 6
  • 22
  • 37

1 Answers1

29

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;
Mike Fal
  • 12,418
  • 2
  • 47
  • 60