CODEWITHSUNDEEP
sql-serverazure-sql-database
Max Buzyak
Max Buzyak

Reputation: 55

Permission to retrieve server state data in Azure SQL

I am trying to run this query inside a stored procedure:

DECLARE @Params NVARCHAR(MAX)
SELECT  @Params = event_info 
FROM    sys.dm_exec_input_buffer(@@SPID, CURRENT_REQUEST_ID())

In order to catch the execution parameters of a stored procedure and print them if necessary (very useful for debugging). So for my [dbo] user is working wonderful, but the problem is when the [AppUser] (with limited permission) tries to execute the same stored procedure, this error appears:

VIEW SERVER PERFORMANCE STATE permission was denied on object 'server', database 'master'.

I have tried to create the user in master and assign to him:

  1. GRANT VIEW DATABASE STATE TO [AppUser]: did not work
  2. RANT VIEW SERVER STATE TO [AppUser]: not supported in Azure SQL
  3. ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER [AppUser]: did not work.

I know that I can print the execution parameters by myself or log it to for example dbo.traceTable, in fact is what I was doing until I discovered this query that is much simple and easier to use.

So, is there any permission I can assign to [AppUser] in order to have permission to retirve data of the query?

Thanks in advance!

EDIT:

Seems like I had to re-login after Role assignation of: ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER [AppUser] in order to take the changes.

Now my problem is that the initial query does not work properly with dapper, but this an another story...

Upvotes: 2

Views: 1890

Answers (1)

Bhavani
Bhavani

Reputation: 5317

I created login to Azure sql database with admin authentication created new login in master database using below code:

create login1 with password = '<password>'

I created a user with above login in my required database and assigned role to the user.

USE [database]
GO
CREATE USER <user> FOR LOGIN <login>
GO
ALTER ROLE db_owner ADD MEMBER <user>
GO

enter image description here

I login with the new user into the database and try to execute below code:

DECLARE @Params NVARCHAR(MAX)
SELECT  @Params = event_info 
FROM    sys.dm_exec_input_buffer(@@SPID, CURRENT_REQUEST_ID())

I got below error:

enter image description here

I login into master database with admin authentication and added below role to the new login

ALTER SERVER ROLE ##MS_ServerStateReader##
  ADD MEMBER login1;  
GO

enter image description here

I query the same by connecting master database instead of my database with new login authentication it executed successfully without any error:

enter image description here

Upvotes: 3

Related Questions