Versions Compared

Old Version 4

changes.mady.by.user Former user

Saved on

compared with

New Version Current

changes.mady.by.user Roderick Bowser

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Info

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 and use the master database to run commands:

Code Block
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 following role below.:

Code Block
USE master; 
EXEC sp_addrolemember 'loginmanager', 'datical_ref_user';
Info

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 a 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.

Info

You must be connected to the database instance you want to use with Datical DB while granting permissions.

Code Block
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 and use the master database to run commands:

Code Block
USE master; 
CREATE LOGIN [datical_login] WITH PASSWORD=N'<password>';
CREATE USER [datical_user] FOR LOGIN [datical_login];
Info

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 Forecast and Deploy (not Packager) operations 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.

Info

You must be connected to the database instance you want to use with Datical DB while granting permissions.

Code Block
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';