Rajendra Gupta

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

February 25, 2022 by

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.

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.

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.

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.

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.


Rajendra Gupta
2,003 Views