Permissions comparison between on-premises SQL Server and Azure SQL Database

The database contains business, customer sensitive and confidential data. Therefore, securing the database environment is the prime responsibility of a database administrator. If you plan to host your databases in Microsoft Azure, you can choose the platform as a service solution – Azure SQL Database. The Azure SQL DB is a fully managed database service; therefore, you do not get full access to SQL Server features, databases.

This article will explore the permissions assigned for an admin user at the Azure SQL Server (master database) and user database level. This article will help you understand the activity and task you can perform on the Azure SQL database compared to on-premises SQL Server.

SQL Server authentication components

The main component of the authentication in SQL Server is as below.

Securables: The securable resources in the Server, Database and Schema categories for which SQL Server grants access
- Server category: Server roles, logins, databases, endpoints, availability groups and server roles etc
- Database: Database user, role, schema, application roles, schema, certificate, full-text catalog etc
- Schema: Table, procedure, function, view, synonym
Permissions: The permissions control actions we assign or deny for accessing the securable
Principals: The principals are the entities, such as logins, database users to which we assign the permissions

Requirements

In this article, for running the queries, we use the following SQL Server environment.

Azure SQL Database: If you do not have any Azure SQL DB, follow the instructions and deploy a free-tier or basic tier SQL DB
- My Azure DB administrator login: sqladmin
SQL Server Instance: I use SQL Server 2019 Linux version to run queries in an on-premises environment. You can choose any of the Windows or Linux SQL Server

Get Effective Permissions of Server Principal

SQL Server system function sys.fn_my_permissions() is useful for listing permissions for users, logins in an instance. It returns all permissions that are assigned for a specific securable.

You can explore the sys.fn_my_permissions() syntax, permisios returned from URL :
https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-builtin-permissions-transact-sql?view=sql-server-ver15

To check my permissions, I executed the script on Azure SQL Database and SQL Server on Linux.

SELECT entity_name, permission_name FROM fn_my_permissions(NULL, 'SERVER');

The results are as below.

entity_name	permission_name	Azure SQL Database	On-Prem SQL
server	CONNECT SQL	No	Yes
server	SHUTDOWN	No	Yes
server	CREATE ENDPOINT	No	Yes
server	CREATE ANY DATABASE	Yes	Yes
server	CREATE AVAILABILITY GROUP	No	Yes
server	ALTER ANY LOGIN	Yes	Yes
server	ALTER ANY CREDENTIAL	No	Yes
server	ALTER ANY ENDPOINT	No	Yes
server	ALTER ANY LINKED SERVER	No	Yes
server	ALTER ANY CONNECTION	No	Yes
server	ALTER ANY DATABASE	Yes	Yes
server	ALTER RESOURCES	No	Yes
server	ALTER SETTINGS	No	Yes
server	ALTER TRACE	No	Yes
server	ALTER ANY AVAILABILITY GROUP	No	Yes
server	ADMINISTER BULK OPERATIONS	No	Yes
server	AUTHENTICATE SERVER	No	Yes
server	EXTERNAL ACCESS ASSEMBLY	No	Yes
server	VIEW ANY DATABASE	Yes	Yes
server	VIEW ANY DEFINITION	Yes	Yes
server	VIEW SERVER STATE	Yes	Yes
server	CREATE DDL EVENT NOTIFICATION	No	Yes
server	CREATE TRACE EVENT NOTIFICATION	No	Yes
server	ALTER ANY EVENT NOTIFICATION	No	Yes
server	ALTER SERVER STATE	Yes	Yes
server	UNSAFE ASSEMBLY	No	Yes
server	ALTER ANY SERVER AUDIT	No	Yes
server	CREATE SERVER ROLE	No	Yes
server	ALTER ANY SERVER ROLE	No	Yes
server	ALTER ANY EVENT SESSION	No	Yes
server	CONNECT ANY DATABASE	Yes	Yes
server	IMPERSONATE ANY LOGIN	No	Yes
server	SELECT ALL USER SECURABLES	No	Yes
server	CONTROL SERVER	No	Yes

Get Effective Permissions of database principal

Database Permissions

To check permissions on the database level, use the following T-SQL statement.

SELECT * FROM fn_my_permissions(NULL, 'Database');

Azure SQL Database:

entity_name	permission_name
database	CREATE TABLE
database	CREATE VIEW
database	CREATE PROCEDURE
database	CREATE FUNCTION
database	CREATE RULE
database	CREATE DEFAULT
database	BACKUP DATABASE
database	BACKUP LOG
database	CREATE DATABASE
database	CREATE TYPE
database	CREATE ASSEMBLY
database	CREATE XML SCHEMA COLLECTION
database	CREATE SCHEMA
database	CREATE SYNONYM
database	CREATE AGGREGATE
database	CREATE ROLE
database	CREATE MESSAGE TYPE
database	CREATE SERVICE
database	CREATE CONTRACT
database	CREATE REMOTE SERVICE BINDING
database	CREATE ROUTE
database	CREATE QUEUE
database	CREATE SYMMETRIC KEY
database	CREATE ASYMMETRIC KEY
database	CREATE EXTERNAL LANGUAGE
database	CREATE EXTERNAL LIBRARY
database	CREATE FULLTEXT CATALOG
database	CREATE CERTIFICATE
database	CREATE DATABASE DDL EVENT NOTIFICATION
database	CREATE USER
database	CONNECT
database	CONNECT REPLICATION
database	CHECKPOINT
database	SUBSCRIBE QUERY NOTIFICATIONS
database	AUTHENTICATE
database	SHOWPLAN
database	ALTER ANY USER
database	ALTER ANY ROLE
database	ALTER ANY APPLICATION ROLE
database	ALTER ANY COLUMN ENCRYPTION KEY
database	ALTER ANY COLUMN MASTER KEY
database	ALTER ANY SCHEMA
database	ALTER ANY ASSEMBLY
database	ALTER ANY DATABASE SCOPED CONFIGURATION
database	ALTER ANY DATASPACE
database	ALTER ANY EXTERNAL DATA SOURCE
database	ALTER ANY EXTERNAL FILE FORMAT
database	ALTER ANY EXTERNAL LIBRARY
database	ALTER ANY EXTERNAL LANGUAGE
database	ALTER ANY EXTERNAL STREAM
database	ALTER ANY EXTERNAL JOB
database	ALTER ANY MESSAGE TYPE
database	ALTER ANY CONTRACT
database	ALTER ANY SERVICE
database	ALTER ANY REMOTE SERVICE BINDING
database	ALTER ANY ROUTE
database	ALTER ANY FULLTEXT CATALOG
database	ALTER ANY SYMMETRIC KEY
database	ALTER ANY ASYMMETRIC KEY
database	ALTER ANY CERTIFICATE
database	ALTER ANY SECURITY POLICY
database	SELECT
database	INSERT
database	UPDATE
database	DELETE
database	REFERENCES
database	EXECUTE
database	ALTER ANY DATABASE DDL TRIGGER
database	ALTER ANY DATABASE EVENT NOTIFICATION
database	ALTER ANY DATABASE AUDIT
database	ALTER ANY DATABASE EVENT SESSION
database	KILL DATABASE CONNECTION
database	VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
database	VIEW ANY COLUMN MASTER KEY DEFINITION
database	VIEW DATABASE SECURITY STATE
database	VIEW DATABASE PERFORMANCE STATE
database	VIEW DATABASE STATE
database	VIEW SECURITY DEFINITION
database	VIEW DEFINITION
database	TAKE OWNERSHIP
database	ALTER
database	ALTER ANY MASK
database	UNMASK
database	EXECUTE ANY EXTERNAL SCRIPT
database	ADMINISTER DATABASE BULK OPERATIONS
database	ALTER ANY SENSITIVITY CLASSIFICATION
database	VIEW ANY SENSITIVITY CLASSIFICATION
database	VIEW CRYPTOGRAPHICALLY SECURED DEFINITION
database	ENABLE LEDGER
database	ALTER LEDGER
database	VIEW LEDGER CONTENT
database	CONTROL

SQL Server database

entity_name	permission_name
database	CREATE TABLE
database	CREATE VIEW
database	CREATE PROCEDURE
database	CREATE FUNCTION
database	CREATE RULE
database	CREATE DEFAULT
database	BACKUP DATABASE
database	BACKUP LOG
database	CREATE DATABASE
database	CREATE TYPE
database	CREATE ASSEMBLY
database	CREATE XML SCHEMA COLLECTION
database	CREATE SCHEMA
database	CREATE SYNONYM
database	CREATE AGGREGATE
database	CREATE ROLE
database	CREATE MESSAGE TYPE
database	CREATE SERVICE
database	CREATE CONTRACT
database	CREATE REMOTE SERVICE BINDING
database	CREATE ROUTE
database	CREATE QUEUE
database	CREATE SYMMETRIC KEY
database	CREATE ASYMMETRIC KEY
database	CREATE EXTERNAL LANGUAGE
database	CREATE EXTERNAL LIBRARY
database	CREATE FULLTEXT CATALOG
database	CREATE CERTIFICATE
database	CREATE DATABASE DDL EVENT NOTIFICATION
database	CONNECT
database	CONNECT REPLICATION
database	CHECKPOINT
database	SUBSCRIBE QUERY NOTIFICATIONS
database	AUTHENTICATE
database	SHOWPLAN
database	ALTER ANY USER
database	ALTER ANY ROLE
database	ALTER ANY APPLICATION ROLE
database	ALTER ANY COLUMN ENCRYPTION KEY
database	ALTER ANY COLUMN MASTER KEY
database	ALTER ANY SCHEMA
database	ALTER ANY ASSEMBLY
database	ALTER ANY DATABASE SCOPED CONFIGURATION
database	ALTER ANY DATASPACE
database	ALTER ANY EXTERNAL DATA SOURCE
database	ALTER ANY EXTERNAL FILE FORMAT
database	ALTER ANY EXTERNAL LIBRARY
database	ALTER ANY EXTERNAL LANGUAGE
database	ALTER ANY MESSAGE TYPE
database	ALTER ANY CONTRACT
database	ALTER ANY SERVICE
database	ALTER ANY REMOTE SERVICE BINDING
database	ALTER ANY ROUTE
database	ALTER ANY FULLTEXT CATALOG
database	ALTER ANY SYMMETRIC KEY
database	ALTER ANY ASYMMETRIC KEY
database	ALTER ANY CERTIFICATE
database	ALTER ANY SECURITY POLICY
database	SELECT
database	INSERT
database	UPDATE
database	DELETE
database	REFERENCES
database	EXECUTE
database	ALTER ANY DATABASE DDL TRIGGER
database	ALTER ANY DATABASE EVENT NOTIFICATION
database	ALTER ANY DATABASE AUDIT
database	ALTER ANY DATABASE EVENT SESSION
database	KILL DATABASE CONNECTION
database	VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
database	VIEW ANY COLUMN MASTER KEY DEFINITION
database	VIEW DATABASE STATE
database	VIEW DEFINITION
database	TAKE OWNERSHIP
database	ALTER
database	ALTER ANY MASK
database	UNMASK
database	EXECUTE ANY EXTERNAL SCRIPT
database	ADMINISTER DATABASE BULK OPERATIONS
database	ALTER ANY SENSITIVITY CLASSIFICATION
database	VIEW ANY SENSITIVITY CLASSIFICATION
database	CONTROL

Check object-level permissions in SQL Server and Azure SQL Database

To check the permissions for a specific object as well using the function sys.dn_my_permissions. For example, here, we check permission for the [SalesLT].[Product] table.

SELECT * FROM fn_my_permissions('SalesLT.Product', 'OBJECT')

Check database-level roles in Azure SQL Database and on-premises SQL database

Azure SQL DB has additional database-level roles in comparison to the on-premises SQL database. Run the following sys.database_principals on both databases.

SELECT name, type_desc FROM sys.database_principals

On-premises SQL Server database

SQL Server	SQL_USER
public	DATABASE_ROLE
dbo	SQL_USER
guest	SQL_USER
INFORMATION_SCHEMA	SQL_USER
sys	SQL_USER
db_owner	DATABASE_ROLE
db_accessadmin	DATABASE_ROLE
db_securityadmin	DATABASE_ROLE
db_ddladmin	DATABASE_ROLE
db_backupoperator	DATABASE_ROLE
db_datareader	DATABASE_ROLE
db_datawriter	DATABASE_ROLE
db_denydatareader	DATABASE_ROLE
db_denydatawriter	DATABASE_ROLE

Azure SQL Database

Azure	DATABASE_ROLE
dbo	SQL_USER
guest	SQL_USER
INFORMATION_SCHEMA	SQL_USER
sys	SQL_USER
sqladmin	SQL_USER
dbmanager	DATABASE_ROLE
loginmanager	DATABASE_ROLE
db_owner	DATABASE_ROLE
db_accessadmin	DATABASE_ROLE
db_securityadmin	DATABASE_ROLE
db_ddladmin	DATABASE_ROLE
db_backupoperator	DATABASE_ROLE
db_datareader	DATABASE_ROLE
db_datawriter	DATABASE_ROLE
db_denydatareader	DATABASE_ROLE
db_denydatawriter	DATABASE_ROLE

It has the following additional roles.

dbmanager: The user in this role can create, delete the database.
loginmanager: User can create, delete logins in Azure’s virtual master database

Conclusion

This article discusses the permissions comparison between the Azure SQL Database and on-premises SQL Server. You can refer to Microsoft documentation for information on each database, server-level roles and permissions.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023
Learn the PostgreSQL COALESCE command - January 19, 2023