Advanced configuration of SQL Server login accounts

<< Click to Display Table of Contents >>

Navigation:  Bizagi Studio > Bizagi Studio system requirements > Database requisites > SQL Server requisites  >

Advanced configuration of SQL Server login accounts

Overview

Security policies within companies may restrict the use of the sa login (used by default in Bizagi) or a login account having the sysadmin server role, for the connection to a SQL Server database.

 

Due to this reason, we illustrate what login accounts are needed for Bizagi Studio, and how to create them with the necessary rights.

 

 

Login account types

These are listed in a hierarchical order, in which the top login account can accomplish what the lower one does, but not vice versa.

The most important login account is that one for the process administrator (others are optional).
 

LOGIN ACCOUNT TYPE / PROFILE

RIGHTS

OBJECTIVE / DESCRIPTION

Bizagi Database administrator (optional)

Server role: sysadmin

This login account will not be used from Bizagi Studio.

 

The objective of this account is to administrate the other login accounts (i.e, create the login account described next), without having to rely on the actual DBA.

Process administrator

Server role: public

Master database rights:
Create database, backup database, grant view any definition.

Configuring the use of this login account in Bizagi Studio should be done at the Bizagi Server where the project will be hosted.

 

By means of this login, you will be able to: create new or update existing Bizagi projects, and to perform project deployments.

Process analyst (optional)

Server role: public

DB Owner of the specific database of the Bizagi project.

Configuring the use of this login account in Bizagi Studio should be done for the process analysts' workstations (when in a collaborative teamwork setup). They would all share the same login account.

 

It will allow you to work on a Bizagi project (model the process, define the data model and user interfaces, create business rules, etc).

 

 

Creating the login accounts

While using the SQL Server Management Studio, make sure you are connected to your instance with a super administrator account (i.e, with the sa default one).

You may create additional login accounts, according to the involved profiles, as description below.

 

 

Create the system administrator login account (optional)

Create a new system administrator login account, in order to manage other login accounts and stop using the sa one.

 

To do so, locate the Security item and create a login account that meets with the following:

 

Uses SQL Server authentication mode:

 

21CreateSysadmin_login

 

Make sure it has the sysadmin server role:

 

21CreateSysadmin_roles

 

Verify that the login account is enabled and granted connection, at the Status options:

 

21CreateSysadmin_grant

 

Click Ok.

 

Create the process administrator login account

Use the system administrator login account created previously (or the sa login account if you skipped this step).

Run the following script to create one login account with rights to create, update or deploy projects:

 

 

/* Create login */

USE [master]

 

/* Create login */

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'process_admin_user')

 DROP LOGIN [process_admin_user]

GO

CREATE LOGIN [process_admin_user] WITH PASSWORD=N'process_admin_password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER LOGIN [process_admin_user] ENABLE

GO

 

GRANT BACKUP DATABASE TO [process_admin_user]

GO

GRANT CREATE DATABASE TO [process_admin_user]

GO

 

 

Consider:

process_admin_user: Should be replaced with the name of your login account.

process_admin_password: Should be replaced with the password you set for your login account.

 

To verify the proper creation of this user, you may run:

use [master]

exec sp_helprotect NULL, 'process_admin_user'

 

use [tempdb]

exec sp_helprotect NULL, 'process_admin_user'

 

 

You should see the following information:

 

22CreateProcAdmin_verify

 

 

At this point, the login account has been created.

Keep in mind that this login account will be used in the Bizagi Studio configured at the Bizagi Server when creating a new project.

 

 

Create the process analyst login account (optional)

Use the system administrator login account created in the first step (or the sa login account if you skipped that step).

Run the following script to create one login account with rights to work on a Bizagi project once it has been already created:

 

/* Create login */

USE [master]

GO

 

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'proc_analyst')

 DROP LOGIN [proc_analyst]

GO

CREATE LOGIN [proc_analyst] WITH PASSWORD=N'proc_analyst_password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER LOGIN [proc_analyst] ENABLE

GO

 

/* Grant permission on server */

GRANT VIEW ANY DEFINITION TO [proc_analyst]

GO

 

/* Deny view other databases */

DENY VIEW ANY DATABASE TO [proc_analyst]

 

Consider:

proc_analyst: Should be replaced with the name of your login account.

proc_analyst_password: Should be replaced with the password you set for your login account.

 

To verify the proper creation of this user, you may run:

use [master]

exec sp_helprotect NULL, 'proc_analyst'

 

 

You should see the following information:

 

23CreateProcAnalyst_verify

 

At this point, the login account has been created.

Keep in mind that this login account will be used the same, in all Bizagi Studio workstations for team members joining to an existing project.

 

 

Important

Make sure you acknowledge these considerations:

 

1. DBOwnership to the process analyst after database creation

If you do create a login account for your process analyst profile, you will need to run the following script once the project has been created, to be sure that the process analyst login is set as the database owner of the project in Bizagi:

 

use [your_Bizagi_database]

exec sp_changedbowner 'proc_analyst'

 

 

2. Tempdb consideration on instance restart

Whenever the database instance is restarted, keep in mind that any definition at the tempdb database is cleared up.

Therefore, for the process administrator login account, you will need to rerun the part of the script that creates the user on tempdb:

 

/* Create user on tempdb */

USE [tempdb]

GO

IF EXISTS (SELECT name FROM sys.database_principals WHERE name = 'process_admin_user')

BEGIN

   DROP USER [process_admin_user]

END

CREATE USER [process_admin_user] FROM LOGIN [process_admin_user]

GO

 

/* Grant permission on tempdb */

GRANT ALTER TO [process_admin_user]

GO

GRANT CREATE TABLE TO [process_admin_user]

GO

GRANT SELECT TO [process_admin_user]

GO

 

Otherwise, this login account will not be able to create or update a project, or perform a deployment.

 

3. Un-authorized connections

Whenever the configuration in Bizagi Studio uses a login account which is not entirely authorized, you may redefine the login account involved for your Bizagi Studio installation.

You may do this from the prompt shown while attempting to use a feature that requires authorized credentials, or from the option to reconfigure the database connection.