SQL SERVER DBA, Linux and Azure: May 2022

SQL Server – Get Size of All Databases in MB and GB

 The Following script  will retrieve the size of all your databases in MB and GB.


SELECT d.NAME
    ,ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0) Size_MBs
    ,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.NAME
ORDER BY d.NAME
--Exclude Offline Databases
 SELECT d.NAME
    ,ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0) Size_MBs
    ,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 
--and d.state_desc='online' -- Skip system databases
GROUP BY d.NAME
ORDER BY d.NAME

Database level roles in Azure SQL Database

 Database security is critical for an organization to protect unauthorized access to the sensitive and critical data stored in the database objects. There are many layers of security in terms of infrastructure security, database authentication, authorization, encryption.

Once you create a new Azure SQL DB using Azure portal or Azure CLI, the provisioning process deploys a logical Azure SQL Server in the Azure region. You get a virtual master database for managing the configuration and security at the server level. It also configures a server-level principal as a database owner of the Azure database. This account has the highest permissions in Azure SQL DB(PaaS) and has sufficient rights to manage server and database-level security.


The following table summarizes the difference in database security management of Azure SQL Database and on-premises SQL Database.


Fixed Database Roles in Azure DB

Expand the Azure SQL DB and navigate to security -> Roles -> Database Roles to get a list of available fixed database roles, expand the Azure SQL DB and navigate to Security -> Roles -> Database Roles. You get the following fixed-database roles.


Additional roles in the virtual master database

If you look at the same database roles in the virtual master database, you get additional database roles, as shown below.


Azure Database contains additional security roles: loginmanager for creating logins and dbmanager for creating databases.

Note: The users in the master database can only be added to these database roles.

Loginmanager role:

Users in the loginmanager database role can create and delete the logins in the master database.

dbmanager role

The dbmanager role allows the user to create a database, delete a database as a database owner. It allows users to connect the Azure database as a DBO user that contains all DB permissions. The user in the role does not have permission to access other databases that they do not own.