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

Create Login User

Ensure the user has access to read the metadata.

USE [master]
GO
CREATE LOGIN [datical_login] WITH PASSWORD=N'password_goes_here', DEFAULT_DATABASE=[datical_db], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
GRANT VIEW ANY DEFINITION TO datical_login
GO

Create Database User

Create a user (datical_user) for databases under Datical DB management. Assign the needed Microsoft SQL Server fixed roles to the user.

Step 1: Identify the appropriate permissions for the Datical User

DATICAL PERMISSIONS

Needed for all environments (note, some of the permissions are optional depending on criteria shown beside each one).

Role/Permission	When is this required?	Permissions
db_ddladmin	This is required if not connecting as the database owner.	Run any Data Definition Language (DDL) command in a database.
db_datawriter	This is required if not connecting as the database owner.	Add, delete, or change data in all user tables.
db_datareader	This is required if not connecting as the database owner.	Read all data from all user tables.
db_securityadmin	This is required if grants are allowed in scripts.	Perform grants to all objects in the database.
VIEW DATABASE STATE	This is required for Datical operations.	Read data from dynamic management views scoped to the database.

DATICAL PACKAGER PERMISSIONS

Required on Reference DB environments only. These permissions are in addition to the above DATICAL_ROLE permissions.

Role

When is this required?

Permissions

db_backupoperator

This is required on the Reference Database if not connecting as the database owner.

Perform backups of the database.

dbcreator

This is required on the Reference Database Server if you are not a sysadmin or the database owner.

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 default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.”

“RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.”

sysadmin

This is required if using Ephemeral Database for MSSQL and copy without data mode. See Configure Deployment Packager to Use a SQL Server Ephemeral Database.

Perform the DBCC CLONEDATABASE command to create a schema-only copy of the database.

Step 2: Grant The Appropriate Permissions As Defined Above to the Datical User

Note, additional permissions are typically added to the Reference DB environments as discussed above.

Example script to assign roles to datical_user.

DATICAL_USER

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_securityadmin', N'datical_user'
GO
GRANT VIEW DATABASE STATE to [datical_user]
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [datical_user]
GO

Example script to assign additional roles to datical_user for the Reference DB (and packaging) environments only.