Return
-
Title
Permissions for Azure SQL Database Monitoring -
Description
Foglight for Azure SQL can be used for granting permissions on several levels.
The following sections detail the permissions that can be granted to users of Azure SQL at each level, and instructhow to manually run the grant privileges script. -
Resolution
Granting Permissions to Azure SQL Users
Database-level Permissions
The following permissions are granted at the database level:- CREATE USER—the lowest permission level, which only allows accessing each database for reading its metadata.
IMPORTANT: The CREATE USER permission does not come as part of the script, as the command is not supported as part of a batch in Azure SQL.
- VIEW DATABASE STATE—required for reading metadata information.
Running the Grant Permissions Script
The file used for granting permissions manually, SQLAzureGrantPrivilegesScript.sql, can be downloaded by clicking the link View script under the Instances table, accessible via either of the following methods:
- When running the Monitor Azure SQL Database wizard, the script link is in the Insufficient Privileges dialog screen.
- In the Cartridges - Components for Download screen.
IMPORTANT: Running this file requires one of the following server roles:- Server admin
- Active Directory admin
- Member of the db_owner
To manually run the Grant Permissions script:
- Run the CREATE USER command on a database to be monitored.
Upon successful completion of this command, the login becomes a user in the specific database, and therefore able to read the database’s metadata. - Open the DBSS_Azure_Permissions_User_Databases.sql file in SQL Server Management Studio (SSMS).
- Find the Select@LoginName = ? section at the beginning of this file.
- Replace the question mark with the login name to which the requested permissions are to be assigned.
Execute the script.