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 which 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 user.

Quick tip icon

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 and

  • 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 result in the following message:

Message for the dedicated model when the permissions are missing

This message is shown because the Luka user does not have the necessary permissions to create any new object in the chosen database, in this case, table and database trigger, and without them present in the linked database, tracking changes against that database will not be possible.

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
  • 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:

Right-click context menu in the Object Explorer panel for Azure SQL Database

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:

The Database Properties window for the newly created user with only the Connect permission granted

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:

The Database Properties window for the newly created user with all necessary permissions granted for the dedicated model

Quick tip icon

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,
	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
  • 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), after the Shared option is selected under the Development model tab in the Source control setup window and the Next button is clicked, will result in the following message:

Message for the shared model when the permissions are missing

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 any database DDL trigger
  • Select
  • Insert
  • Update
  • Execute
  • Delete
  • Create table
  • Create procedure
  • Create schema
  • Alter any schema
  • View database state

Quick tip icon

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 Database Properties window for the newly created user with all necessary permissions granted for the shared model

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 ANY DATABASE DDL TRIGGER, 
	SELECT,
	INSERT,
	UPDATE,
	EXECUTE,
	DELETE,
	CREATE TABLE,
	CREATE PROCEDURE,
	CREATE SCHEMA,
	ALTER ANY 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.

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:

Query results for the newly created user with all necessary permissions granted for the dedicated model

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

Query results for the newly created user with all necessary permissions granted for the shared model

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