Hello Everyone!
In this post we will discuss, “How to setup Logins with minimal permissions to monitor Azure SQL Databases?”
Recently, I and other teammates were brain storming the idea of writing a monitoring application for Azure databases and that is when we came across this question. We wanted to make sure the admins can use a login with very few permissions while logging into our application to monitor the databases, we wanted them to feel secure and trust that the application will not have capability to change anything in the databases, in fact not even be able read the data from the databases.
Let us get to work!
Step1: Create a login
Login to the server instance using your admin account.
–Create a login with password
Create LOGIN APPLOGIN with password=’abcd123?’
If you attempt to access the instance with the above login you will get the below error
******
Cannot open database “master” requested by the login. The login failed.
Login failed for user ‘APPLOGIN’.
This session has been assigned a tracing ID of ‘<>’. Provide this tracing ID to customer support when you need assistance. (Microsoft SQL Server, Error: 4060)
******
To be able to login to the server we need to add this login to the master database as user, with DEFAULT_SCHEMA = GUEST
–Create user under master database
CREATE USER [APPUSER] FOR LOGIN [APPLOGIN] WITH DEFAULT_SCHEMA=[guest]
GO
When we login using APPLOGIN into management studio, we will able to see the databases listed, however we will not be able to view any of the user tables in the user databases.
Step 2: Setup a database
Let us create a database to monitor. We have to use the admin login as we cannot create a database using the APPLOGIN
–Create TEST_DB
CREATE DATABASE TEST_DB
GO
Switch to TEST_DB database
–Create a simple table under TEST_DB, we will use this to test permissions.
CREATE TABLE TEST_TABLE
(
c1 int NOT NULL
CONSTRAINT PK_sample_table PRIMARY KEY (c1)
)
GO
Step 3: Add user and permissions
To be able to run any queries against TEST_DB database using APPLOGIN we need to add it as a user.
–Add login as user under TEST_DB
CREATE USER [APPUSER] FOR LOGIN [APPLOGIN] WITH DEFAULT_SCHEMA=[guest]
GO
To monitor any database we need “VIEW DATABASE STATE” and “VIEW DEFINITION” Permissions for the user account
–Provide “VIEW DATABASE STATE” permission to the user [APPUSER]
GRANT VIEW DATABASE STATE TO APPUSER
–Provide “VIEW DEFINITION” permission to the user [APPUSER]
GRANT VIEW DEFINITION TO APPUSER
Step 4: Verify permissions
Now that we have the Login, user, and database to monitor in place, let’s verify if the user is able to select any tables in the TEST_DB, because our main aim is to make sure the monitoring LOGIN has minimal permissions
Login to the server using APPLOGIN and take a connection with TEST_DB database and run the below query.
select * from TEST_TABLE
Boom! We get the below error
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ‘TEST_TABLE’, database ‘TEST_DB’, schema ‘dbo’.
You can run other queries like, create table, update a row etc. to make sure there the permissions are at minimum.
Step 5: Monitor the database
We will be using the queries in below article to monitor Azure SQL databases
http://msdn.microsoft.com/library/azure/ff394114.aspx
Make sure you are connected to TEST_DB using APPLOGIN (monitoring login)
–To monitor database size
SELECT SUM(reserved_page_count)*8.0/1024
FROM sys.dm_db_partition_stats;
GO
SELECT SUM(reserved_page_count)*8.0/1024
FROM sys.dm_db_partition_stats;
GO
— Find top 5 queries
SELECT TOP 5 query_stats.query_hash AS “Query Hash”,
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS “Avg CPU Time”,
MIN(query_stats.statement_text) AS “Statement Text”
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END
– QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
GO
Finally, we have a login created with minimal permission to monitor the Azure SQL databases. Using this login there is no risk of any data read or data modification.
