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

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:

1. 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.
2. 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.
3. 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.
          
     

4. 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.

its a contained database, which permission need to give to user, if user wanted to update the data

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

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:

1. 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.
2. 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).
3. 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.
4. 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.

how to rewrite the stored procedure