Versions Compared

Old Version 3

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.

...

See Database Users, Roles, and Passwords for a general database setup for Datical DB. 

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 the user you will create has access to read the metadata.use the master database to run commands:

Code Block
CREATE LOGIN [datical_ref_login] WITH PASSWORD=N
'password_goes_hereN' GO
'<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:

Code Block
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 databases under Datical DB management and assign the needed roles to the user:

Step 1: Identify the appropriate permissions for the Datical User

Datical permissions, which are needed for all environments.

Info

Some of the permissions are optional depending on the criteria shown in the table.

a database where Packager operation will be performed:

Role/Permission

When is this required?

Permissions

db_owner

This role is required

if you need to perform all configuration and some maintenance activities on the database

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

db_loginmanager

This is required if grants are allowed in scripts. If you have this role, you can create and delete logins in the virtual master database.      

Perform grants to all objects in the database.

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.

Datical packager permissions, which are required on reference database environments only. These permissions are additional to the DATICAL_ROLE permissions.

Role

When is this required?

Permissions

dbcreator

This is required to create an ephemeral copy of the database.   

Can create and delete databases. A member of the dbmanager role that creates a database, becomes the owner of that database which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the dbmanager role do not necessarily have permission to access databases that they do not own.

Also, a user with the dbmanager role can perform database restores.

If the database being restored does not exist, the user must have CREATE DATABASE permissions

to

be able to execute RESTORE. If

the database

exists, RESTORE permissions are default to members of the sysadmin and dbmanager fixed server roles and the owner (dbo) of the database

.

RESTORE permissions are given to roles in which membership information is always available to the server. You can check a fixed database role membership only when the database is accessible and undamaged, which is not always the case when RESTORE is executed. For this reason, members of the db_owner fixed database role do not have RESTORE permissions.

If you want to use Deploy Packager, assign the roles of db_owner and dbmanager to the user. If you don’t want to use Deploy Packager, assign the roles of db_datawriter or db_datareader. Additionally, if you need to deploy the objects listed below, grant the db_ddladmin role to the user.

Info

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

...

Code Block
ALTER ANY ASSEMBLY
ALTER ANY ASYMMETRIC KEY
ALTER ANY CERTIFICATE
ALTER ANY CONTRACT
ALTER ANY DATABASE DDL TRIGGER
ALTER ANY DATABASE EVENT NOTIFICATION
ALTER ANY DATASPACE
ALTER ANY FULLTEXT CATALOG
ALTER ANY MESSAGE TYPE
ALTER ANY REMOTE SERVICE BINDING
ALTER ANY ROUTE
ALTER ANY SCHEMA
ALTER ANY SERVICE
ALTER ANY SYMMETRIC KEY
CHECKPOINT
CREATE AGGREGATE
CREATE DEFAULT
CREATE FUNCTION
CREATE PROCEDURE
CREATE QUEUE
CREATE RULE
CREATE SYNONYM
CREATE TABLE
CREATE TYPE
CREATE VIEW
CREATE XML SCHEMA COLLECTION 
REFERENCES

...

permissions

...

Info

Additional permissions are typically added to the reference database environments.

Example script to assign roles to datical_user.

DATICAL_USER

Code Block
USE [database_1] GO 
CREATE USER [datical_user] FOR LOGIN [datical_login]
GO
;
EXEC sp_addrolemember
N
'db_ddladmin',
N
'datical_user'
GO
;
EXEC sp_addrolemember
N
'db
_datareader', N'datical
_
user' GO EXEC sp_addrolemember N'db_
datawriter',
N
'datical_user';
GO 
EXEC sp_addrolemember
N
'db_
loginmanager
datareader',
N
'datical_user'
GO GRANT VIEW DATABASE STATE to [datical_user] GO ALTER SERVER ROLE [dbmanager] ADD MEMBER [datical_user] GO

Example script to assign additional roles to datical_user for the reference database environments and packaging only:

DATICAL_USER

Code BlockUSE [database_1] GO EXEC sp_addrolemember N'dbmanager', N'datical_user' GO
;