Minimal permissions for Azure SQL Database in database source control

Applies to

ApexSQL Source Control

Summary

This article will explain the minimal needed users’ permissions for Azure SQL Database when ApexSQL Source Control is used as a database source control tool.

Description

The database source control environment supports working on Azure SQL Database which applies that more than one user will be connected to the same SQL Server. In that case, the necessary server/database user permissions for working on a specific database must be defined before the database development can start.

One of the differences between the SQL Server database and Azure SQL Database is in the needed login/user settings/permissions for continuous work on the chosen database. For Azure SQL Database is assumed there is only one SQL Server admin that cannot be changed. All other created logins and roles do not have and cannot be granted this level of server permissions.

The specific set of the database user permissions dependent on the chosen development model is required, when the database source control for Azure SQL Database is done with ApexSQL Source Control. During the linking process the necessary framework objects, dependent on the chosen development model, will be created in the same database that is being linked to the source control repository. More about the framework objects can be found in the ApexSQL Source Control framework objects inventory article.

For the purpose of this article, two users will be created for both development models. For the dedicated development model, the Luka user will be created, and for the shared development model the Pera user. The only permission these newly created database users have is the Connect permission since it is the default user permission for Azure SQL Database users.

Quick tip: Before granting any additional permission to any of these users they should be mapped to the databases that will work on in the database source control

Before granting any permission to any database user, be sure the user logged to SQL Server has enough permissions to do that. In our case, the Lima user will be used as an Azure SQL Database login user with the system admin permissions.

Dedicated development model

Linking a database in the dedicated development model to a source control repository will create the following framework objects under the dbo schema in the same database:

• Table:
  • ApexSQL_SourceControl_DatabaseLog

• Function:
  • ApexSQL_SourceControl_FrameworkVersion

• Database level trigger:
  • ApexSQL_SourceControl_DDLTrigger

Trying to link a database to the source control repository in the dedicated development model with the Luka user that only has the Connect permission, will open the Source control setup window, but when the Dedicated development model is selected and the Next button or any other tab is clicked, the following message will be raised:

The following permissions must be granted to the database user in order to successfully link the chosen database to source control and perform the basic actions in the database source control:

• Connect
• Alter any database DDL trigger
• Select
• Insert
• Update
• Execute
• Delete
• Create table
• Create function
• Alter dbo schema
• View database state

Since the Connect permission has already been granted by default to the Luka user, here will be explained how to grant the rest of the listed necessary permission through SQL Server Management Studio GUI and using the SQL scripts.

To add any permission to the database user, in our case the Luka user, right-click on the database to which the Luka user is mapped, in the Object Explorer panel, and choose the Properties command:

In the Database Properties window, under the Permissions tab, when the database user Luka is selected the granted permissions will be listed under the Explicit tab. For the Luka user only the Connect permission is granted so far:

Check the box in the Grant column for all needed permissions (Alter any database DDL trigger, Select, Insert, Update, Execute, Delete, Create table, and View database state), and click the OK button:

Quick tip: The Alter dbo schema permission must be granted to the database user using the SQL since it cannot be granted through SQL Server Management Studio GUI for the Azure SQL Database

The other way to grant all the necessary permissions for the database source control to the database user is by using SQL. All listed permissions will be granted to the Luka user after the following SQL script is executed against the database to which the user is mapped:

GRANT ALTER ANY DATABASE DDL TRIGGER
	,SELECT
	,INSERT
	,UPDATE
	,EXECUTE
	,DELETE
	,CREATE TABLE
	,CREATE FUNCTION
	,VIEW DATABASE STATE
	TO [Luka]

GRANT ALTER
	ON SCHEMA::[dbo]
	TO [Luka]

After this, the chosen database can be linked to the source control repository in the dedicated development model by the Luka user without any issues.

Shared development model

Using ApexSQL Source Control as a database source control tool, for linking a database to the source control repository in the shared development model, requires a couple of more database user permissions than linking in the dedicated development model. This is related directly to the framework objects.

Choosing the shared development model in the database source control means that during the linking process of an Azure SQL Database to the source control repository, the framework objects will be created in the same database that is being linked, but under the specific schema, the ApexSQL_SourceControl schema:

• Tables:
  • Objects
  • Polices
  • ChangeLog
  • UserMappings
  • ExcludedObjects

• Procedures
  • UpdateObjects
  • UpdateExcludedObjects

• Function
  • FrameworkVersion

• Database level trigger
  • ApexSQL_SourceControl_DDLTrigger

Trying to link a database to a source control repository in the shared development model with the Pera user (that only has granted the Connect permission), will raise the message with the missing permissions against a database that is being linked when the Shared development model is chosen and the Next button is clicked or any other tab in the Source control setup window:

To successfully link a database in the shared development model to the source control repository and start with the database source control, the database user, the Pera user, will need the following permissions:

• Connect
• Alter
• Select
• Insert
• Update
• Execute
• Delete
• Create table
• Create procedure
• Create function
• Create schema
• View database state

Quick tip: In the database source control, the View database state permission is not explicitly needed for linking a database to the source control repository, but it is needed for showing the database object state and appropriate context menu options in the Object Explorer pane.

Granting all necessary permissions to the Pera user can be done through SQL Server Management Studio GUI in the Database Properties window under the Permissions tab:

The same permissions can be granted to the Pera user using the SQL, by executing the following script against a database to which is user mapped to:

GRANT ALTER
	,SELECT
	,INSERT
	,UPDATE
	,EXECUTE
	,DELETE
	,CREATE TABLE
	,CREATE PROCEDURE
	,CREATE FUNCTION
	,CREATE SCHEMA
	,VIEW DATABASE STATE TO [Pera]

Linking a database in the shared development model to a source control repository with the Pera user will now go without any issue.

• Note: For any other user who will link the same database in the shared development model, the list of needed permissions will be shorter:
  • SELECT
  • INSERT
  • UPDATE
  • EXECUTE
  • DELETE
  • VIEW DATABASE STATE

Verification of the granted permissions

The already granted database user permissions should be checked/verified before the additional permissions (needed for the database source control) are added/granted.

Verification of the granted permissions for the database user can be done using the following query against the chosen database:

SELECT * FROM fn_my_permissions(NULL, 'DATABASE')

For the Luka user, with granted permissions needed for using the dedicated development model, the results will be:

For the Pera user, with granted permissions needed for using the shared development model, the results, among other permissions that come with the Alter permission, will be:

These two database users, the Luka and Pera users, with their set of granted permissions can start with the database source control.

Affected versions

The SQL inventory system explained in this article is implemented in ApexSQL Source Control 2021 version and higher. In the previous versions of ApexSQL Source Control, the scalar-valued function FrameworkVersion will not be created and corresponding permission is not needed.

From the ApexSQL Source Control 2021 version, the Alter any database DDL trigger and Alter any schema permissions are replaced with the Alter permission for databases linked in the shared development model for Azure SQL Database.