| Table of Contents |
|---|
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.
| Code Block | ||
|---|---|---|
| ||
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. |
| db_ssisadmin | This is required if using SSIS via Datical. | SSIS: list, view, run, export all packages. Import, delete, and change all packages and package roles. |
| db_ssisltduser | This is required if using SSIS via Datical. | SSIS: list all packages; view, run, export own packages. Import packages, change and delete own packages. |
| db_ssisoperator | This is required if using SSIS via Datical. | SSIS: list, view, run, export all packages. Run all packages in SQL Server Agent. |
| 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.” |
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.
...