azure db permissions

[earthcoder]

Hello i been setting up this tool, but some of the functions dont seem to work i get the following errors in logs:

Microsoft.Data.SqlClient.SqlException (0x80131904): User does not have permission to perform this action.
Cannot alter the event session 'DBADash_1', because it does not exist or you do not have permission.
Cannot alter the event session 'DBADash_1', because it does not exist or you do not have permission.
Cannot alter the event session 'DBADash_1', because it does not exist or you do not have permission.

I applied the following security to my account i am using but azure moans about them, so not sure how to fix, i tried googling and chatgpt but everything i try the commands dont work.

GRANT ALTER EVENT SESSION ON DATABASE TO Reader_dbdash;
GRANT CONTROL ON DATABASE TO Reader_dbdash;
GRANT VIEW SERVER STATE TO [Reader_dbdash]
GRANT VIEW ANY DATABASE TO [Reader_dbdash]
GRANT CONNECT ANY DATABASE TO [Reader_dbdash]
GRANT VIEW ANY DEFINITION TO [Reader_dbdash]
GRANT ALTER ANY EVENT SESSION TO [Reader_dbdash] /* Required if you want to use slow query capture */

Msg 40521, Level 16, State 1, Line 36
Securable class 'server' not supported in the server scope in this version of SQL Server.

We use the following:
SQL Hyperscale Elastic pool

I tried to add the event but comes up with

Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@SlowQueryThreshold".
Msg 137, Level 15, State 2, Line 19
Must declare the scalar variable "@EventSessionTemplate".
Msg 137, Level 15, State 2, Line 23
Must declare the scalar variable "@UseDualSession".
Msg 137, Level 15, State 2, Line 31
Must declare the scalar variable "@EventSessionTemplate".
Msg 156, Level 15, State 1, Line 35
Incorrect syntax near the keyword 'ELSE'.
Msg 137, Level 15, State 2, Line 59
Must declare the scalar variable "@UseDualSession".
Msg 137, Level 15, State 2, Line 91
Must declare the scalar variable "@UseDualSession".
Msg 156, Level 15, State 1, Line 107
Incorrect syntax near the keyword 'ELSE'.

But cant find what the prams should be..

Also get this:

Microsoft.Data.SqlClient.SqlException (0x80131904): VIEW SERVER PERFORMANCE STATE permission was denied on object 'server', database 'master'.
The user does not have permission to perform this action.
VIEW SERVER PERFORMANCE STATE permission was denied on object 'server', database 'master'.
The user does not have permission to perform this action.

any help be great!

[DavidWiseman]

It looks like the docs need updating for assigning minimal permissions in Azure. Try running this in the master database:

ALTER SERVER ROLE ##MS_ServerStateReader##
ADD MEMBER Reader_dbdash

And this in the user database:

GRANT CREATE ANY DATABASE EVENT SESSION TO Reader_dbdash;
GRANT ALTER ANY DATABASE EVENT SESSION TO Reader_dbdash;

Hope this helps.

[earthcoder]

added these will monitor and let you know

[earthcoder]

seems that the event session is working now but still get the following on master.

Microsoft.Data.SqlClient.SqlException (0x80131904): VIEW SERVER PERFORMANCE STATE permission was denied on object 'server', database 'master'.
The user does not have permission to perform this action.
VIEW SERVER PERFORMANCE STATE permission was denied on object 'server', database 'master'.
The user does not have permission to perform this action.

[earthcoder]

looks like i didnt add master to the configs soon as i did seems to be working now will monitor

[DavidWiseman]

Glad it's working now - thanks for letting me know.

It's the ##MS_ServerStateReader## role membership that is needed to provide the VIEW SERVER PERFORMANCE STATE permission.

https://learn.microsoft.com/en-us/azure/azure-sql/database/security-server-roles?view=azuresql