Applies to
Summary
In this article, all needed database user permissions for linking Amazon Relational Database Service (Amazon RDS) to a source control repository and working with it in the database source control using ApexSQL Source Control will be explained.
When working with Amazon RDS be sure to use the endpoint name of the server that contains amazonaws.com in its name for the connection in SQL Server Management Studio.
Description
When talking about the Amazon RDS environment it is applied several users are working on the same shared SQL Server/database at the same time. For that type of environment, the necessary set of permissions must be granted to those database users working on the database development.
In this type of environment usually is one admin user with permissions to create new logins/users and grant them all necessary permissions for the database development. Based on that, work in the database source control requires the specific permissions granted to the database user as well, to link a database to a source control repository and track changes on it.
The database source control done using ApexSQL Source Control applies that the additional objects will be created in the linked database, based on the chosen development model. For these objects, called the framework objects, the user must be granted with a specific set of permissions.
For this article, since the necessary database source control permissions depend on the chosen development model, for each of them will be created user (for dedicated and shared development model). The Deja user will cover the dedicated development model and the Mara user will cover the shared development model.
After these database users are created, the only granted default permissions they have are:
-
SQL Server permissions:
- Connect SQL
- View any database
-
Database permissions
- Connect
- View any column encryption key definition
- View any master key definition
Dedicated development model
The dedicated development model applies there will be only one user working on the chosen database, only one user changing the database. The first step in the database source control is linking a database to a source control repository. The following framework objects created in the chosen database during the linking process are:
- dbo.ApexSQL_SourceControl.ChangeLog table – will be created under the Tables node, the System Tables sub-node
- ApexSQL_SourceControl_DDL Trigger – will be created under the Programmability node, the Database Triggers sub-node
- dbo.ApexSQL_SourceControl_FrameworkVersion function will be created under the Programmability – Functions – Scalar-valued Functions node
Try to link the database to a source control repository with the Deja user with the default Server/database permissions will open the Source control setup window. When the Dedicated development model is chosen and the Next button or any other tab in the Source control setup window is clicked, the following message will be raised:
Since the database source control cannot be done without the listed framework objects, the following permission need to be granted to the Deja user, so it can create and update them as needed:
- Connect
- Alter any database DDL trigger
- Select
- Insert
- Update
- Execute
- Delete
- Create table
- Create function
- View definition
- Alter dbo schema
Granting the listed permissions to the Deja user can be done in two ways, using SQL Server Management Studio UI or executing the SQL scripts.
To grant permission using SQL Server Management Studio, the Deja user is mapped to the specific database. In the Object Explorer panel, right-click on that database, and click the Properties context menu option:
When the Database Properties window is opened, go under the Permission tab, select the Deja user and in the Grant column check for all needed (above listed) permissions and click the OK button:
|
Quick tip: The View definition database permissions should be granted in order to “see” all database changes made against the database objects in the dedicated development model. |
Granted database permissions to the Deja user can be checked under the Effective tab in the same window:
|
Quick tip: For granting the Alter dbo schema permission will be used a SQL script. |
Granting the same set of database source control permissions to the Deja user can be done when the following SQL script is executed:
GRANT ALTER ANY DATABASE DDL TRIGGER, SELECT, INSERT, UPDATE, EXECUTE, DELETE, VIEW DEFINITION, CREATE FUNCTION, CREATE TABLE TO [Deja] GRANT ALTER ON SCHEMA::[dbo] TO [Deja]
No matter which way is chosen for granting the database source control necessary permissions to the Deja user, after this linking a database in the dedicated development model and working with it using the Deja user will go perfectly.
Shared development model
When more than one database developer works on the same shared database at the same time, the best practice for the database source control is to choose the shared development model during the linking process. The shared development model will require additional permissions than the dedicated development model since different framework objects are created in the linked database.
The following framework objects created in the chosen database during the linking process under the ApexSQL_SourceControl schema (for the dedicated development model the dbo schema is being used for framework objects are:
-
Under the Table node the following tables will be created:
- Objects
- Polices
- ChangeLog
- UserMappings
- ExcludedObjects
-
Under the Procedures node the following procedures will be created:
- UpdateObjects
- UpdateExcludedObjects
-
Under the Programmability node, the Database Triggers sub-node, the following trigger will be created:
- ApexSQL_SourceControl_DDLTrigger
If the Mara user, with the default database permissions (Connect, View any column encryption key definition, View any master key definition) tries to link a database to a source control repository, the following message will appear when the Next button is clicked in the Development model tab when the Shared development model is chosen:
With all listed database permissions in the message, database user, in our case the Mara user, must have the following permissions to link a database to a source control repository and work on it in the database source control:
- Connect
- Alter
- Select
- Insert
- Update
- Execute
- Delete
- Create table
- Create procedure
- Create schema
- View definition
|
Quick tip: The View definition database permissions must be granted in order to all database users “see” all database users and their changes made against the database objects in the shared development model. |
To grant all necessary database permissions to the Mara user, right-click on the database in the Object Explorer panel, and from the context menu, click the Properties option, to open the Database Properties window:
Check the Grant column under the Explicit tab for all above listed necessary permissions and click the OK button:
The granted database permissions to the Mara user can be found under the Effective tab in the same window:
Granting the same set of database permissions to the Mara user can be done when the following SQL script is executed:
GRANT ALTER ANY DATABASE>, SELECT, INSERT, UPDATE, EXECUTE, DELETE, CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE SCHEMA, VIEW DEFINITION TO [Mara]
With this set of database permissions, the Mara user can link a database to a source control repository and work with a database in the database source control perfectly.
These database permissions are required only for the first user who will link the database in the shared development model to a source control repository. All other users should have only these database source control permissions to link and work on the same shared database:
- Select
- Insert
- Update
- Execute
- Delete
- View definition
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 Amazon RDS.
