patternsqlMajor
Give permission to select from system tables
Viewed 0 times
tablespermissionsystemgiveselectfrom
Problem
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:
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:
Still, when marie tries to perform the query above, the error is:
What am I doing wrong?
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.spidThe 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 marieStill, 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?
Solution
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;Code Snippets
GRANT VIEW SERVER STATE TO marie;Context
StackExchange Database Administrators Q#34612, answer score: 29
Revisions (0)
No revisions yet.