Applies to
Summary
This article explains the user’s minimal SQL Server and database permissions needed to work with a database using ApexSQL Source Control. Also, the article covers how to grant needed permissions to the user.
Description
The following SQL Server and database permissions are required for the user who wants to link a database to a source control repository:
It is sufficient for the SQL login to be a member of the public server role:
Also, it is sufficient for the SQL user or a group to have public role membership for the specific database that will be linked:
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.
To complete the list of all needed SQL Server and database permissions, the following permissions need to be granted to the specific user or a group against a database that will be linked to a source control repository:
- Connect
- Insert
- Select
- Execute
- Delete
- Update
- Create table
- Create function
- Alter any database DDL trigger
- Note: The Connect permission should be added by default when the database mapping is done for the user
After the above permissions are granted the following permission should be added:
- Alter against dbo schema
|
Quick tip: Make sure that the appropriate user is created and mapped to a database to grant the minimum SQL Server and database permissions required |
For this article, Mara is used as an example for the SQL database user. 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:
Under the Permissions tab, highlight the user in the User or roles section (in this case we’ll use Mara), and under the Explicit tab in the bottom section, check the box in the Grant column for the appropriate permissions, Connect, Update, Insert, Alter any database DDL trigger, Select, Delete, Create function, Create table and Execute. Once this is done, click the OK button to apply these settings.
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:
Besides the above-granted permissions, the user will need the Alter permission against the dbo schema. To grant this one, locate the dbo schema under the database node. It can be found by expanding the nodes, Database – Security – Schemas – dbo:
Under the Properties window, switch to the Permissions tab, and for the selected user (in this case Mara) check the box in the bottom section under the Grant column, for the Alter permission:
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 SQL Server and database permissions to the user (in this case Mara) against the DevelopmentDB database:
GRANT ALTER ANY DATABASE DDL TRIGGER ,EXECUTE ,UPDATE ,DELETE ,INSERT ,SELECT ,CREATE TABLE ,CREATE FUNCTION TO [Mara] GRANT ALTER ON SCHEMA::[dbo] TO [Mara]
When all necessary SQL Server and database permissions are granted, the database can be linked to a repository using the dedicated model. More information about how to link a database to a source control repository can be found in the How to link a SQL Server database to a source control system article.
|
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 sysadmin permission) than the ones explained. However, these are the minimum SQL Server and database permissions required |
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.
|
Quick tip: If the user is a sysadmin on a SQL Server (which is the highest level permission), the Control server permission does not have to be granted additionally |
- Note: Be sure not to change the SQL Server and database permissions for the first user who linked a database in the shared development model. If that happens, to track changes on the linked database, the Server level DDL trigger should be recreated by the user with the Control server permission
To grant this permission, right-click on a SQL 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:
This allows the first user to link the first database on a SQL Server. For more information about what is created for the first time, check the ApexSQL Source Control framework objects inventory article.
|
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 SQL Server and database permissions. against a database that hosts additional objects created by ApexSQL Source Control (in this case ApexSQL database):
- Connect
- Insert
- Select
- Update
- Execute
- Delete
|
Quick tip: Make sure that the appropriate user is created in the ApexSQL database to grant the minimum permissions required |
Let’s grant all SQL Server and database permissions to a sample user, Mara. First, the needed permissions against the ApexSQL database will be granted. Right-click ApexSQL database, select the Properties menu item, and switch to the Permissions tab:
Make sure that the correct user is selected in the Users or roles section, mark checkboxes in the Grant column for the following permissions: Delete, Execute, Insert, Connect, Update, Select.
To complete the list of all needed SQL Server and database permissions, an additional one is needed on a Server level:
- View any definition
To grant that one, right-click on a Server node in the Object Explorer pane, select the Properties item, and switch to the Permissions node:
Check the box for the appropriate login (in this case Mara) in the Grant column, for the View any definition permission.
This allows Mara to link an already linked database, or to link any other database from the same SQL Server instance in the shared development model.
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 [ApexSQL] GRANT DELETE ,EXECUTE ,INSERT ,SELECT ,UPDATE TO [Mara] USE [master] GRANT VIEW ANY DEFINITION TO [Mara]
This set of the SQL Server and database permissions should be granted to each user who wants to link and work with the database in the shared model.
FAQs
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 SQL Server and database permissions to link a database in the shared development model?
A: If a user doesn’t have any of the minimal SQL Server and database permissions the following information message will be shown in the Source control setup window under the Development model tab, when the Shared option is chosen and the Next button is clicked:
-
For the first user:
-
For the second user:
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 permission required for the first user only when linking a database on SQL Server 2012 version and higher, 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.
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.
