Security requirements for monitoring Azure SQL Database
The login used by miniDBA to connect to Azure SQL Database must have the following permissions:
- VIEW SERVER PERFORMANCE STATE (granted once in master database)
- VIEW DATABASE PERFORMANCE STATE (granted for each database)
- ALTER ANY DATABASE EVENT SESSION (granted for each database)
You can either:
- Use the Azure SQL Server admin or Entra Admin logins (they have required permissions and can be found in the Azure portal)
OR
- Create a new login with the required permissions
The follwing is an example of how to create a new login with the required permissions. Bear in mind this is NOT a contained user, it HAS TO BE a
login in the master database with associated users in each database.
--In master database:
CREATE LOGIN minidba WITH PASSWORD = 'password';
CREATE USER minidba FROM LOGIN minidba;
ALTER SERVER ROLE ##MS_ServerPerformanceStateReader## ADD MEMBER minidba;
--Then add the user to each database
--Connect to each database and run:
CREATE USER [minidba] FROM LOGIN [minidba];
GRANT ALTER ANY DATABASE EVENT SESSION TO minidba;