See Database Users, Roles, and Passwords for a general database setup for Datical DB.
The Datical Packager process requires elevated privileges on a REF Azure SQL Database as an ephemeral database is created and dropped as part of the Backup and Restore operations. The below instructions show how to setup separate Logins and Users for a REF database versus a non-REF database.
Create a REF Datical Login and User
Make sure that you are connected to the master database as the Server admin:
USE master; CREATE LOGIN [datical_ref_login] WITH PASSWORD=N'<password>'; CREATE USER [datical_ref_user] FOR LOGIN [datical_ref_login]; EXEC sp_addrolemember 'dbmanager', 'datical_ref_user'; |
The dbmanager role is a Server-level role required for managing and creating databases. Additionally, if the Datical User needs the ability to create and manage logins include the role below.
USE master; EXEC sp_addrolemember 'loginmanager', 'datical_ref_user'; |
The default Azure password complexity rules are the following: minimum length of 8 characters, minimum of 1 uppercase character, minimum of 1 lowercase character, minimum of 1 number.
Create a REF Database User
Create a user (datical_ref_user) for database where Packager operation will be performed:
Role/Permission | When is this required? | Permissions |
|---|---|---|
db_owner | This role is required for REF databases and the Packager process to create an ephemeral copy of the database during Backup and Restore. | Manage fixed-database role membership, configuration, and maintenance activities. |
VIEW DATABASE STATE | This is required for some Datical operations such as using Approximate Row Counts. | Read data from dynamic management views scoped to the database. |
You must be connected to the database instance you want to use with Datical DB while granting permissions.
USE [REF database]; CREATE USER [datical_ref_user] FOR LOGIN [datical_ref_login]; EXEC sp_addrolemember 'db_owner', 'datical_ref_user'; |
Create a non-REF Datical Login and User
Make sure that you are connected to the master database as the Server admin:
USE master; CREATE LOGIN [datical_login] WITH PASSWORD=N'<password>'; CREATE USER [datical_user] FOR LOGIN [datical_login]; |
The default Azure password complexity rules are the following: minimum length of 8 characters, minimum of 1 uppercase character, minimum of 1 lowercase character, minimum of 1 number.
Create a non-REF Database User
Create a user (datical_user) for database where Packager operation will be performed:
Role/Permission | When is this required? | Permissions |
|---|---|---|
db_ddladmin | This is required if you do not connect as the database owner. | Run any Data Definition Language (DDL) command in a database. |
db_datawriter | This is required if you do not connect as the database owner. | Add, delete, or change data in all user tables. |
db_datareader | This is required if you do not connect as the database owner. | Read all data from all user tables. |
VIEW DATABASE STATE | This is required for some Datical operations such as using Approximate Row Counts. | Read data from dynamic management views scoped to the database. |
You must be connected to the database instance you want to use with Datical DB while granting permissions.
USE [non-REF database]; CREATE USER [datical_user] FOR LOGIN [datical_login]; EXEC sp_addrolemember 'db_ddladmin', 'datical_user'; EXEC sp_addrolemember 'db_datawriter', 'datical_user'; EXEC sp_addrolemember 'db_datareader', 'datical_user'; |