Permissions in ApexSQL Source Control (4216308)

Applies to ApexSQL Source Control

Summary This article explains what are minimal permissions needed for the user to version control a database using ApexSQL Source Control. In addition, the article covers how to grant needed permissions to user.

Description

Dedicated model

When working in the dedicated model, additional objects that ApexSQL Source Control creates will be hosted by the linked database. Each linked database in the dedicated model will have a database DDL trigger and a system table, as explained in the ApexSQL Source Control framework objects inventory article.

The following permissions are required for the user who wants to link and version control a database in the dedicated model:

It is sufficient for the SQL login to be a member of the public server role:

Click To See Full Image.

Also, it is sufficient for the SQL user or a group to have public role membership for the specific database:

Click To See Full Image.

Besides these, permissions which need to be granted to the specific user or a group against a database that will be version controlled are:

• Insert
• Select
• Execute
• Update
• Connect
• Alert any DDL trigger

After the above permissions are granted the following permission should be added:

• Alter against dbo schema

Click To See Full Image.

Quick tip: Make sure that the appropriate user is created and mapped to a database in order to grant the minimum permissions required.

For the purpose of the article, Adam is used as an example for the SQL database user. In order to grant these permissions, right click on a database in the Object Explorer pane, select the Properties from the context menu, and choose the Permissions tab to initiate the following:

Click To See Full Image.

Under the Permissions tab, highlight the user in the User or roles section (in this case we’ll use Adam), and under the Explicit tab in the bottom section, check the box in the Grant column for the appropriate permissions, Connect, Execute, Create table, Delete, Update, Alter any database DDL trigger, Insert and Select. Once this is done, click the OK button to apply these settings.

In order to verify that all permissions are granted, initiate the same tab again and switch from the Explicit tab, to the Effective tab. Verify that the appropriate permissions are granted as shown in the image below:

Click To See Full Image.

Besides the above granted permissions, the user will need the Alter permission against the dbo schema. In order to grant this one, locate the dbo schema under the database node. It can be found by expanding the nodes, Database->Security->Schemas->dbo:

Click To See Full Image.

Under the Properties window, switch to the Permissions tab and for the selected user (in this case Adam) check the box in the bottom section under the Grant column, for the Alter permission:

Click To See Full Image.

In addition to granting permissions by using the GUI (as explained above), the same can be achieved using T-SQL. The following is a query that will grant the necessary permissions to the user (in this case Adam) against the AdventureWorks2014 database:

USE [AdventureWorks2014]
GO

GRANT ALTER ANY DATABASE DDL TRIGGER
	TO [Adam]
GO

GRANT DELETE
	TO [Adam]
GO

GRANT EXECUTE
	TO [Adam]
GO

GRANT INSERT
	TO [Adam]
GO

GRANT SELECT
	TO [Adam]
GO

GRANT UPDATE
	TO [Adam]
GO

GRANT ALTER
	ON SCHEMA::[dbo]
	TO [Adam]
GO

GRANT CREATE TABLE
	TO [Adam]
GO

When all necessary permissions are granted, the database can be linked to a repository using the dedicated model.

Click To See Full Image.

Quick tip: Since the dedicated model means that each developer has a separate instance of SQL Server and a local copy of a database on a local machine, it is most likely that the user will have higher permissions (db_owner or even a sysadmin permission) than the ones explained. However, these are the minimum required ones.

Shared model

When working in the shared model, the first user who links the first database on the specific SQL Server instance will need:

• Control server permission against the SQL Server instance

This is because a server level DDL trigger needs to be created.

Click To See Full Image.

Quick tip: Is the user is a sysadmin on a server (which is the highest level permission), the Control server permission does not have to be granted additionally.

In order to grant this permission, right click on a server node and choose the Properties context menu item, select the appropriate user from the list, and check the box in the Grant column for the Control server permission, under the Permissions tab:

Click To See Full Image.

This allows the first user to link the first database on a server. For more information about what is created for the first time, check the ApexSQL Source Control framework objects inventory article.

Click To See Full Image.

Quick tip: Since a new (separate) database is used to host framework objects, all additional permissions are related specifically to that database, and not the one that is being linked.

For better understanding, a default database (called ApexSQL) for storing additional ApexSQL Source Control objects is used in this case.

Each new user who wants to link the same database or any other database from the same SQL Server instance will need the following set of permissions against a database that hosts additional objects created by ApexSQL Source Control (in this case ApexSQL database):

• Connect
• Insert
• Select
• Update
• Execute

Click To See Full Image.

Quick tip: Make sure that the appropriate user is created in the ApexSQL database in order to grant minimum permissions required.

Let’s add permissions to a sample user, John, against ApexSQL database. Right click ApexSQL database, select the Properties menu item, and switch to the Permissions tab:

Click To See Full Image.

Make sure that the correct user is selected in the Users or roles section, check boxes in the Grant column for the following permissions: Delete, Execute, Insert, Connect, Update, Select

Besides these permissions, additional one is needed on a server level:

• View any definition

In order to add that one, right click on a server node in the Object Explorer pane, select the Properties item, and switch to the Permissions node:

Click To See Full Image.

Check the box for the appropriate login (in this case John) in the Grant column, for the View any definition permission.

This allows John to link already linked database, or to link any other database from the same SQl Server instance.

Besides giving these permissions through the UI, the following is a SQL script that can be used to achieve the same using T-SQL:

USE [AdventureWorks2014]
GO

GRANT DELETE
	TO [John]
GO

USE [ApexSQL]
GO

GRANT EXECUTE
	TO [John]
GO

USE [ApexSQL]
GO

GRANT INSERT
	TO [John]
GO

USE [ApexSQL]
GO

GRANT SELECT
	TO [John]
GO

USE [ApexSQL]
GO

GRANT UPDATE
	TO [John]
GO

USE [master]
GO

GRANT VIEW ANY DEFINITION
	TO [John]
GO

This set of permissions should be granted to each user who wants to link and work with database in the shared model.

Q: What if I already have db_owner permissions against a database I want to link in the dedicated model?

A: In that case, there is no need to perform any of the additional grants, since all minimal permissions are already covered.

Q: What if I don’t have any of the necessary granted permissions?

A: If a user doesn’t have any of the minimal permission the following window message will be shown:

Click To See Full Image.

Q: Do I have to grant Insert, Select, Delete, Update permissions on a database level?

A: No, these permissions are required only against tables created by ApexSQL Source Control, thus you can grant these for those tables

Q: Is the Control server required for the first user only when linking a database, or even after linking?

A: The Control server permission is required only for the first linking of any database on a server. As soon as the first one is linked, permissions required for other users are sufficient.