about permission, view server performance state denied in paas server database

Question

about permission, view server performance state denied in paas server database

Naga Durga Devi Kannuri - Admin 20

i have read write permission to particular database in paas server, but while user run the update statement, it was throwing an error, like view server performance state denied, but when i try to give server performance state permission, throwing an error like this permission cant run in server, it is telling that cant execute view server performance state permission state in paas contained database, then what is the solution for this. kindly give resolution steps -by user should update the data

Adithya Prasad K 1,060 Reputation points Microsoft External Staff Moderator

2025-05-07T13:46:02.3866667+00:00
Hi Naga Durga Devi Kannuri - Admin
It looks like you're running into some permission issues when trying to update your database on the PaaS server. The error message you're seeing indicates that your user doesn't have the necessary permissions to view the server's performance state.

Here's a breakdown of what you can do:

Understand Required Permissions:

To resolve this issue, you typically need VIEW DATABASE STATE permission if you're trying to access dynamic management views (DMVs) relevant to the database you're working in. In some cases, VIEW SERVER PERFORMANCE STATE permission is required, but this is not available within the context of a PaaS contained database.

Granting Permissions:

Since you cannot grant VIEW SERVER PERFORMANCE STATE in a contained database, focus on the VIEW DATABASE STATE permission. Execute the following SQL command to grant this permission to your database user:
GRANT VIEW DATABASE STATE TO [database_user];
Replace [database_user] with your actual database username.

Membership in Server Role:

If your operations require server role permissions like ##MS_ServerStateReader##, you should switch to the master database and run this command for your login:
```sql
ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [login_name];
However, please note that this may not be applicable in a contained database scenario, as server roles are limited in such environments.

Error Reference:

If you see errors related to denying permissions (like the one about VIEW SERVER PERFORMANCE STATE), it's because that permission cannot be granted in a contained database environment.

Hope this helps. Do let us know if you any further queries.
Naga Durga Devi Kannuri - Admin 20 Reputation points

2025-05-07T14:47:36.87+00:00

its a contained database, which permission need to give to user, if user wanted to update the data
Naga Durga Devi Kannuri - Admin 20 Reputation points

2025-05-07T14:53:03.1266667+00:00

Msg 300, Level 14, State 1, Procedure RESOLVE_TRANSACTION_AUDIT_ID, Line 10 [Batch Start Line 0]

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

Msg 297, Level 16, State 1, Procedure RESOLVE_TRANSACTION_AUDIT_ID, Line 10 [Batch Start Line 0]

The user does not have permission to perform this action. im encoutering this error
Adithya Prasad K 1,060 Reputation points Microsoft External Staff Moderator

2025-05-07T15:12:38.9566667+00:00
Hi Naga Durga Devi Kannuri - Admin

Since you’re working in a contained, PaaS-based environment (such as Azure SQL Database), you cannot grant or use server-scoped permissions like VIEW SERVER PERFORMANCE STATE. This permission is designed for on-premises or IaaS-based SQL Server instances and isn’t available—or grantable—in contained databases.

The error message:

Msg 300, Level 14, State 1, Procedure RESOLVE_TRANSACTION_AUDIT_ID, Line 10 VIEW SERVER PERFORMANCE STATE permission was denied on object 'server', database 'master'. Msg 297, Level 16, State 1, Procedure RESOLVE_TRANSACTION_AUDIT_ID, Line 10 The user does not have permission to perform this action.

indicates that the stored procedure RESOLVE_TRANSACTION_AUDIT_ID is trying to access server-level information (likely from a dynamic management view or similar object) that requires this permission. Since you’re in a contained database, even if you are the admin, you cannot acquire or delegate the VIEW SERVER PERFORMANCE STATE permission.

Resolution Steps:

Review the Stored Procedure Code:

Check what part of the RESOLVE_TRANSACTION_AUDIT_ID procedure is attempting to query server-level performance data. For example, look for any references to DMVs or system functions that target the master database.

Modify the Procedure:

Remove or rewrite the code that accesses server-scoped information. If the data being pulled is only used for audit or logging purposes, consider changing the logic to use alternative methods that rely only on database-scoped views (e.g., using VIEW DATABASE STATE where applicable).

Confirm Permissions at the Database Level:

Ensure that your user has all the necessary, allowed permissions (such as VIEW DATABASE STATE, if needed) for the operations you expect to perform.

For instance, you can grant database-level state permissions with:
GRANT VIEW DATABASE STATE TO [YourUser];
However, note that this does nothing to grant any server-level permission.

Test the Update Operation:

Once any server-dependent code is removed or replaced, try running the update statement again to confirm that the error no longer occurs.

Unfortunately, in a contained PaaS scenario, you cannot bypass this limitation by granting a server-scoped permission or modifying server-level settings—as those are abstracted away from you by the service. The only path forward is to adjust the logic that’s triggering the permission request.

Let me know if you need additional assistance with modifying the stored procedure or further guidance on working with contained databases.
Naga Durga Devi Kannuri - Admin 20 Reputation points

2025-05-07T15:26:41.7366667+00:00

how to rewrite the stored procedure
Naga Durga Devi Kannuri - Admin 20 Reputation points

2025-05-07T15:45:42.63+00:00

Yes that stored procedure looking for server level DMV'S
SELECT sqlserver_start_time FROM sys.dm_os_sys_info SELECT transaction_id FROM sys.dm_tran_current_transaction
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

1 answer

Your answer

Adithya Prasad K 1,060 Reputation points Microsoft External Staff Moderator

2025-05-07T13:46:02.3866667+00:00

Hi Naga Durga Devi Kannuri - Admin
It looks like you're running into some permission issues when trying to update your database on the PaaS server. The error message you're seeing indicates that your user doesn't have the necessary permissions to view the server's performance state.

Here's a breakdown of what you can do:

Understand Required Permissions:

To resolve this issue, you typically need VIEW DATABASE STATE permission if you're trying to access dynamic management views (DMVs) relevant to the database you're working in. In some cases, VIEW SERVER PERFORMANCE STATE permission is required, but this is not available within the context of a PaaS contained database.

Granting Permissions:

Since you cannot grant VIEW SERVER PERFORMANCE STATE in a contained database, focus on the VIEW DATABASE STATE permission. Execute the following SQL command to grant this permission to your database user:
GRANT VIEW DATABASE STATE TO [database_user];
Replace [database_user] with your actual database username.

Membership in Server Role:

If your operations require server role permissions like ##MS_ServerStateReader##, you should switch to the master database and run this command for your login:
```sql
ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [login_name];
However, please note that this may not be applicable in a contained database scenario, as server roles are limited in such environments.

Error Reference:

If you see errors related to denying permissions (like the one about VIEW SERVER PERFORMANCE STATE), it's because that permission cannot be granted in a contained database environment.

Hope this helps. Do let us know if you any further queries.
Naga Durga Devi Kannuri - Admin 20 Reputation points

2025-05-07T14:47:36.87+00:00

its a contained database, which permission need to give to user, if user wanted to update the data
Naga Durga Devi Kannuri - Admin 20 Reputation points

2025-05-07T14:53:03.1266667+00:00

Msg 300, Level 14, State 1, Procedure RESOLVE_TRANSACTION_AUDIT_ID, Line 10 [Batch Start Line 0]

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

Msg 297, Level 16, State 1, Procedure RESOLVE_TRANSACTION_AUDIT_ID, Line 10 [Batch Start Line 0]

The user does not have permission to perform this action. im encoutering this error
Naga Durga Devi Kannuri - Admin 20 Reputation points

2025-05-07T15:26:41.7366667+00:00

how to rewrite the stored procedure
Naga Durga Devi Kannuri - Admin 20 Reputation points

2025-05-07T15:45:42.63+00:00

Yes that stored procedure looking for server level DMV'S
SELECT sqlserver_start_time FROM sys.dm_os_sys_info SELECT transaction_id FROM sys.dm_tran_current_transaction
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Answer 1

PratikLad 1,125 Microsoft External Staff Moderator

Hi Naga Durga Devi Kannuri - AdminTo be able to query the sys.dm_os_sys_info DMV the user that you are using to get connected to the server requires VIEW SERVER STATE permission on the server or have administrative privileges on the SQL server.

To create a new login in Azure SQL Database with administrative authentication, I executed the following script in the master database:


create login1 with password = '<password>'

Create 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

Login into master database with admin authentication and add below role to the new login


ALTER SERVER ROLE ##MS_ServerStateReader##

  ADD MEMBER login1;  

GO

And open up a new query window as an admin account privileges and enter the following:


GRANT VIEW SERVER STATE TO [login]

PratikLad 1,125 Reputation points Microsoft External Staff Moderator

2025-05-13T11:49:21.8766667+00:00

Hi Naga Durga Devi Kannuri - Admin, We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
PratikLad 1,125 Reputation points Microsoft External Staff Moderator

2025-05-15T08:40:24.2066667+00:00

Hi Naga Durga Devi Kannuri - Admin, We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

about permission, view server performance state denied in paas server database

1 answer

Your answer