Dynamic management views

Question

Dynamic management views

aarded

Ten Centuries

Points: 1312
More actions
November 18, 2011 at 8:26 am

#246631

Hi,
Current SQL version: 2008 R2 SP1.
Previous version: 2008 SP2.
I have a procedure which executes on our previous version, but not on the current version.
The procedure is executed by the user 'Report' and contains the statement 'EXECUTE AS DMView'. The execution returns the error 'The user does not have permission to perform this action.'
My procedure:
CREATE PROCEDURE [dbo].[spCounters]
WITH EXECUTE AS 'DMView'
AS
SELECT physical_io FROM master..sysprocesses WITH (NOLOCK) WHERE spid = @@SPID
SELECT cntr_value FROM master.sys.dm_os_performance_counters
GO
The above procedure, executed by the user 'Report', fails on the second statement (sys.dm_os...). The first statement is executed, the value physical_io is returned.
Also when I start this procedure as user 'DMView', it fails (normal).
Even when I logon as sa and execute the procedure, it fails with this error.
The DMView user has been granted the 'VIEW SERVER STATE' permission.
What surprised me: When I logon in SSMS with the user DMView, and execute the statement 'SELECT cntr_value FROM master.sys.dm_os_performance_counters', it works fine! No error!
Thanks for all help.

Viewing 15 posts - 1 through 15 (of 17 total)

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

okbangas SSChampion Points: 11773 More actions · Answer 1

First of all, sys.sysprocesses is deprecated, so you should start using a DMV instead of this compatibility view. I think the sum of the reads and writes columns from sys.dm_exec_sessions gives you desired result.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 2

Also when I start this procedure as user 'DMView', it fails (normal).Even when I logon as sa and execute the procedure, it fails with this error.

Can you please explain these two? Failing (normal) & Failing (with Error)

aarded Ten Centuries Points: 1312 More actions · Answer 3

To Krsitian: OK, but sys.sysprocesses works fine.

To Dev: I find it 'normal' because the procedure also failed when I started it with the 'EXECUTE AS DMView' clause.

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 4

'VIEW SERVER STATE' is a permission given to login.

GRANT permission [ ,...n ]

TO <grantee_principal> [ ,...n ] [ WITH GRANT OPTION ]

[ AS <grantor_principal> ]

<grantee_principal> ::= SQL_Server_login

| SQL_Server_login_mapped_to_Windows_login

| SQL_Server_login_mapped_to_Windows_group

| SQL_Server_login_mapped_to_certificate

| SQL_Server_login_mapped_to_asymmetric_key

<grantor_principal> ::= SQL_Server_login

| SQL_Server_login_mapped_to_Windows_login

| SQL_Server_login_mapped_to_Windows_group

| SQL_Server_login_mapped_to_certificate

| SQL_Server_login_mapped_to_asymmetric_key

With your SP you are trying to run as user.

Functions (except inline table-valued functions), Stored Procedures, and DML Triggers

{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }

DDL Triggers with Database Scope

{ EXEC | EXECUTE } AS { CALLER | SELF | 'user_name' }

DDL Triggers with Server Scope and logon triggers

{ EXEC | EXECUTE } AS { CALLER | SELF | 'login_name' }

Queues

{ EXEC | EXECUTE } AS { SELF | OWNER | 'user_name' }

User's scope is limited to Database where as Login's scope is at Server Level. Hope it clarifies your doubt.

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 5

Change you SP to execute it in Login's Context...

EXECUTE AS LOGIN = 'DMView'; -- Context Switch

SELECT physical_io FROM master..sysprocesses WITH (NOLOCK) WHERE spid = @@SPID

SELECT cntr_value FROM master.sys.dm_os_performance_counters

aarded Ten Centuries Points: 1312 More actions · Answer 6

The error can be reproduced as follow:

USE master;

GO

--Create two temporary principals

CREATE LOGIN DMView WITH PASSWORD = '123456';

CREATE LOGIN Report WITH PASSWORD = '123456';

GO

USE tempdb;

GO

CREATE USER DMView FOR LOGIN DMView;

CREATE USER Report FOR LOGIN Report;

GO

USE master;

GRANT VIEW SERVER STATE TO Report

GO

USE tempdb;

GO

CREATE PROCEDURE [dbo].[spCounters]

AS SET NOCOUNT ON

GO

ALTER 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

GO

GRANT EXECUTE ON spCounters TO Report

-- execute in other windows with login=Report

USE tempdb;

GO

EXEC spCounters

GO

-- Cleanup

DROP PROCEDURE [dbo].[spCounters]

DROP LOGIN DMView;

DROP USER DMView;

DROP LOGIN Report;

DROP USER Report;

GO

This returns an error, on the statement

SELECT cntr_value FROM master.sys.dm_os_performance_counters

even if you execute this from within the SSMS and you are logged in with sa.

What am I missing?

aarded Ten Centuries Points: 1312 More actions · Answer 7

Dev (11/21/2011)
Change you SP to execute it in Login's Context...
EXECUTE AS LOGIN = 'DMView'; -- Context Switch
SELECT physical_io FROM master..sysprocesses WITH (NOLOCK) WHERE spid = @@SPID
SELECT cntr_value FROM master.sys.dm_os_performance_counters

Returns the Physical_io and the error:

The user does not have permission to perform this action.

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 8

aarded (11/21/2011)
Dev (11/21/2011)
Change you SP to execute it in Login's Context...
EXECUTE AS LOGIN = 'DMView'; -- Context Switch
SELECT physical_io FROM master..sysprocesses WITH (NOLOCK) WHERE spid = @@SPID
SELECT cntr_value FROM master.sys.dm_os_performance_counters
Returns the Physical_io and the error:
The user does not have permission to perform this action.

I can't run the code you posted below (don't have SQL Server / Client on my system). However I would recommend you to go through following example. In last part I guess you are missing on impersonation.

USE AdventureWorks2008R2;

GO

--Create two temporary principals

CREATE LOGIN login1 WITH PASSWORD = 'J345#$)thb';

CREATE LOGIN login2 WITH PASSWORD = 'Uor80$23b';

GO

CREATE USER user1 FOR LOGIN login1;

CREATE USER user2 FOR LOGIN login2;

GO

--Give IMPERSONATE permissions on user2 to user1

--so that user1 can successfully set the execution context to user2.

GRANT IMPERSONATE ON USER:: user2 TO user1;

GO

--Display current execution context.