Dynamic management views

Question

Post reply

Dynamic management views

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

aarded Ten Centuries Points: 1312 More actions · Answer 1

I have got it working, but don't understand why it works with the procedure:

CREATE PROCEDURE [dbo].[spCounters]

AS

SELECT Reads+Writes FROM master.sys.dm_exec_sessions WHERE session_id=@@SPID

EXECUTE AS LOGIN='DMView'

SELECT cntr_value FROM master.sys.dm_os_performance_counters

REVERT

and not with:

CREATE PROCEDURE [dbo].[spCounters]

WITH EXECUTE AS 'DMView'

AS

SELECT Reads+Writes FROM master.sys.dm_exec_sessions WHERE session_id=@@SPID

SELECT cntr_value FROM master.sys.dm_os_performance_counters

What's the difference?

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

aarded (11/21/2011)
I have got it working, but don't understand why it works with the procedure:
CREATE PROCEDURE [dbo].[spCounters]
AS
SELECT Reads+Writes FROM master.sys.dm_exec_sessions WHERE session_id=@@SPID
EXECUTE AS LOGIN='DMView'
SELECT cntr_value FROM master.sys.dm_os_performance_counters
REVERT
and not with:
CREATE PROCEDURE [dbo].[spCounters]
WITH EXECUTE AS 'DMView'
AS
SELECT Reads+Writes FROM master.sys.dm_exec_sessions WHERE session_id=@@SPID
SELECT cntr_value FROM master.sys.dm_os_performance_counters
What's the difference?

I am confused. It was one of my recommendations earlier but you said it didn't work for you.

EXECUTE AS LOGIN='DMView'

The line above changes the Execution Context for Login 'DMView' which has necessary rights.

WITH EXECUTE AS 'DMView'

The line above changes the Execution Context for User 'DMView' which doesn't have necessary rights.

Understanding Execution Context

http://msdn.microsoft.com/en-us/library/ms187096.aspx

aarded Ten Centuries Points: 1312 More actions · Answer 3

It works after I have made a combination of 2 recommendations:

- 1 of Dev: EXECUTE AS LOGIN

- 1 of MarkusB: add VIEW SERVER STATE to DMView.

Thanks.