July 1, 2010 at 12:57 pm
I'm hoping someone can help me out with an issue I'm having. I want to give access to an SP that query some DMV's and I'm running into some permission issues. The SP queries sys.dm_exec_requests, sys.dm_exec_sql_text, sys.dm_exec_sessions, and sys.dm_exec_query_plan. The error the DB returns with is:
Msg 297, Level 16, State 1, Procedure dba_liveActivity, Line 5
The user does not have permission to perform this action.
I added the 'with execute as' due to some constraints I am required to work under.
ALTER proc [dbo].[dba_liveActivity]
with execute as 'mydomain\administrator'
as
SELECT r.percent_complete, [spid] = r.session_id, r.wait_time,
r.blocking_session_id, [obj] = QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.[dbid]))
+ '.' + QUOTENAME(OBJECT_NAME(t.objectid, t.[dbid])),
t.[text], s.host_name, s.program_name, s.client_interface_name, s.login_name,
s.nt_domain, s.nt_user_name, s.status, s.cpu_time, s.memory_usage,
s.reads, s.writes, s.logical_reads,
[database] = DB_NAME(r.database_id),
r.start_time,
r.[status],
r.command,
r.wait_type,
r.last_wait_type,
qp.query_plan
FROM
sys.dm_exec_requests r with (nolock)
outer APPLY
sys.dm_exec_sql_text(r.[sql_handle]) AS t
left outer JOIN
sys.dm_exec_sessions s with (nolock) on
r.session_id = s.session_id
outer APPLY
sys.dm_exec_query_plan(r.plan_handle) as qp
WHERE
r.session_id <> @@SPID
AND r.session_id > 50
I know the execute as is working. I added select system_user to see what returned and it was the administrator and not the caller name. I'd appreciate any thoughts you may have on this.
On a related note, I tried to use SSMS to give permissions to the underlying DMV's but it complained about having to do this in master. I suspect my default DB was something other than master. I'll have to check it when I get back in.
July 1, 2010 at 1:11 pm
J.D. Gonzalez (7/1/2010)
On a related note, I tried to use SSMS to give permissions to the underlying DMV's but it complained about having to do this in master. I suspect my default DB was something other than master. I'll have to check it when I get back in.
You can't grant permissions to the DMVs, regardless of the DB context. Check BoL for what permissions are required for each DMV.
Not sure about the exec as but are you a sysadmin on that server? I suspect you'll need that level of permission to create a proc that executes at that level. Otherwise it would be possible to escalate privileges, which is a big security risk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2010 at 1:33 pm
I'm a sysadmin on the server I can see the results if I don't include the execute as. But once I add the execute as, I get the errors described on my initial post. BOL seems to indicate that I can grant permissions on DMV's. From BOL:
To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission. This lets you selectively restrict access of a user or login to dynamic management views and functions. To do this, first create the user in master and then deny the user SELECT permission on the dynamic management views or functions that you do not want them to access. After this, the user cannot select from these dynamic management views or functions, regardless of database context of the user.
I may give this a shot in dev just to see if I can make it work. It bugs me when I can't get things to go my way:) I'm not too comfortable with opening up permissions for something that may or may not come up again. Like I always say... "Let's fix the problem not the symptom". But that's another story;)
July 1, 2010 at 1:52 pm
What I was trying (badly) to say is that you would still need the view server state or view database state, you can't grant a permission to avoid the need for that
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply