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: |
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:
•Make sure it has the sysadmin server role:
•Verify that the login account is enabled and granted connection, at the Status options:
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 any of the following scripts to create or assign one login account with rights to create, update or deploy projects:
Create a new process administrator user (to create new projects)
For the next scripts 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.
(Click any of the links)
 | New User SQL Authentication /* Create login for Bizagi Process Admin*/ USE [master] GO /*change the following Variables with the user, Password and Database name before execution*/ DECLARE @UserName VARCHAR(120) = 'process_admin_user' --Write User name to conecct bizagi with the database DECLARE @UserPW NVARCHAR(120) = 'process_admin_password' --Type Password for the user EXEC('CREATE LOGIN [' + @UserName + '] WITH PASSWORD=N''' + @UserPW + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF') EXEC('ALTER LOGIN [' + @UserName + '] ENABLE') EXEC('CREATE USER [' + @UserName + '] FOR LOGIN [' + @UserName + ']') EXEC('GRANT BACKUP DATABASE TO [' + @UserName + ']') EXEC('GRANT CREATE DATABASE TO [' + @UserName + ']') EXEC('GRANT ALTER SERVER STATE TO [' + @UserName + ']') |
| New User Windows authentication /* Create login for Bizagi Process Admin*/ USE [master] GO /*change the following Variables with the user, Password and Database name before execution*/ DECLARE @UserName VARCHAR(120) = 'process_admin_user' --Write User name to conecct bizagi with the database EXEC('CREATE LOGIN [' + @UserName + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master]') EXEC('ALTER LOGIN [' + @UserName + '] ENABLE') EXEC('CREATE USER [' + @UserName + '] FOR LOGIN [' + @UserName + ']') EXEC('GRANT BACKUP DATABASE TO [' + @UserName + ']') EXEC('GRANT CREATE DATABASE TO [' + @UserName + ']') EXEC('GRANT ALTER SERVER STATE TO [' + @UserName + ']') |
Create a new process administrator user and assign this user to an existing database
For the next scripts 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.
•your_Bizagi_database: Should be replaced with the name of your Bizagi database.
(Click any of the links)
| New User to existing DB SQL Authentication /* Create login for Bizagi Process Admin*/ USE [master] GO /*change the following Variables with the user, Password and Database name before execution*/ DECLARE @UserName VARCHAR(120) = 'process_admin_user' --Write User name to conecct bizagi with the database DECLARE @UserPW NVARCHAR(120) = 'process_admin_password' --Type Password for the user DECLARE @DBName VARCHAR(120) = 'your_Bizagi_database' --Specify database name to assign user EXEC('CREATE LOGIN [' + @UserName + '] WITH PASSWORD=N''' + @UserPW + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF') EXEC('ALTER LOGIN [' + @UserName + '] ENABLE') EXEC('CREATE USER [' + @UserName + '] FOR LOGIN [' + @UserName + ']') EXEC('GRANT BACKUP DATABASE TO [' + @UserName + ']') EXEC('GRANT CREATE DATABASE TO [' + @UserName + ']') EXEC('GRANT ALTER SERVER STATE TO [' + @UserName + ']') EXEC(' USE ' + @DBName + ' CREATE USER [' + @UserName + '] FROM LOGIN [' + @UserName + '] ALTER ROLE [db_owner] ADD MEMBER [' + @UserName + '] ALTER ROLE [db_datareader] ADD MEMBER [' + @UserName + '] ALTER ROLE [db_datawriter] ADD MEMBER [' + @UserName + '] ALTER ROLE [rlBA_SQL_BizAgiWebApp] ADD MEMBER [' + @UserName + '] ALTER ROLE [rlBA_SQL_ExecuteBizAgiSPs] ADD MEMBER [' + @UserName + '] DECLARE @sSQL varchar(8000) SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''P'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE ON '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''P'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''TF'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT SELECT ON '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''TF'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''FN'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE on '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''FN'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sys.types WHERE is_user_defined = 1 AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE ON TYPE::'''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sys.types WHERE is_user_defined = 1'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 ') |
| New User to existing DB Windows Authentication /* Create login for Bizagi Process Admin*/ USE [master] GO /*change the following Variables with the user, Password and Database name before execution*/ DECLARE @UserName VARCHAR(120) = 'process_admin_user' --Write User name to conecct bizagi with the database DECLARE @DBName VARCHAR(120) = 'your_Bizagi_database' --Specify database name to assign user EXEC('CREATE LOGIN [' + @UserName + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master]') EXEC('ALTER LOGIN [' + @UserName + '] ENABLE') EXEC('CREATE USER [' + @UserName + '] FOR LOGIN [' + @UserName + ']') EXEC('GRANT BACKUP DATABASE TO [' + @UserName + ']') EXEC('GRANT CREATE DATABASE TO [' + @UserName + ']') EXEC('GRANT ALTER SERVER STATE TO [' + @UserName + ']') EXEC(' USE ' + @DBName + ' CREATE USER [' + @UserName + '] FROM LOGIN [' + @UserName + '] ALTER ROLE [db_owner] ADD MEMBER [' + @UserName + '] ALTER ROLE [db_datareader] ADD MEMBER [' + @UserName + '] ALTER ROLE [db_datawriter] ADD MEMBER [' + @UserName + '] ALTER ROLE [rlBA_SQL_BizAgiWebApp] ADD MEMBER [' + @UserName + '] ALTER ROLE [rlBA_SQL_ExecuteBizAgiSPs] ADD MEMBER [' + @UserName + '] DECLARE @sSQL varchar(8000) SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''P'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE ON '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''P'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''TF'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT SELECT ON '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''TF'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''FN'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE on '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''FN'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sys.types WHERE is_user_defined = 1 AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE ON TYPE::'''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sys.types WHERE is_user_defined = 1'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 ') |
Assign an existing process administrator user to an existing database
For the next scripts consider:
•process_admin_user: Should be replaced with the name of your login account.
•your_Bizagi_database: Should be replaced with the name of your Bizagi database.
(Click the link)
| Assign existing User to existing DB /* Create login for Bizagi Process Admin*/ USE [master] GO /*change the following Variables with the user, Password and Database name before execution*/ DECLARE @UserName VARCHAR(120) = 'process_admin_user' --Write User name to conecct bizagi with the database DECLARE @DBName VARCHAR(120) = 'your_Bizagi_database' --Specify database name to assign user EXEC(' USE ' + @DBName + ' CREATE USER [' + @UserName + '] FROM LOGIN [' + @UserName + '] ALTER ROLE [db_owner] ADD MEMBER [' + @UserName + '] ALTER ROLE [db_datareader] ADD MEMBER [' + @UserName + '] ALTER ROLE [db_datawriter] ADD MEMBER [' + @UserName + '] ALTER ROLE [rlBA_SQL_BizAgiWebApp] ADD MEMBER [' + @UserName + '] ALTER ROLE [rlBA_SQL_ExecuteBizAgiSPs] ADD MEMBER [' + @UserName + '] DECLARE @sSQL varchar(8000) SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''P'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE ON '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''P'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''TF'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT SELECT ON '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''TF'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''FN'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE on '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''FN'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sys.types WHERE is_user_defined = 1 AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE ON TYPE::'''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sys.types WHERE is_user_defined = 1'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 ') |
Once that the script is executed successfully, proceed to execute the Validate_Bizagi_Roles.sql script in order to make sure that the roles and adequate rights are set. Such script validates and adjusts the configuration in question, and it won't perform modifications if it detects that these are unneeded.
To verify the proper creation of this user, you may run:
use [master]
exec sp_helprotect NULL, 'process_admin_user'
use [your_Bizagi_database]
exec sp_helprotect NULL, 'process_admin_user'
You should see the following information:
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 any of the following scripts to create one login account with rights to work on a Bizagi project once it has been already created:
Create a new process analyst user and assign this user to an existing DB
For the next scripts consider:
•process_analyst_user: 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.
•your_Bizagi_database: Should be replaced with the name of your Bizagi database.
(Click any of the links)
| New user to existing DB SQL Authentication /* Create login for Bizagi Process Analyst*/ USE [master] GO /*change the following Variables with the user, Password and Database name before execution*/ DECLARE @UserName VARCHAR(120) = 'process_analyst_user' --Write User name to conecct bizagi with the database DECLARE @UserPW NVARCHAR(120) = 'process_analyst_password' --Type Password for the user DECLARE @DBName VARCHAR(120) = 'your_Bizagi_database' --Specify database name to assign user EXEC('CREATE LOGIN [' + @UserName + '] WITH PASSWORD=N''' + @UserPW + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF') EXEC('ALTER LOGIN [' + @UserName + '] ENABLE') EXEC('CREATE USER [' + @UserName + '] FOR LOGIN [' + @UserName + ']') EXEC('GRANT ALTER SERVER STATE TO [' + @UserName + ']') /* Create user on Bizagi existent Database */ IF EXISTS (SELECT * FROM sys.databases WHERE name = @DBName ) BEGIN EXEC(' USE ' + @DBName + ' CREATE USER [' + @UserName + '] FROM LOGIN [' + @UserName + '] /* Grant permission on Database */ GRANT ALTER TO [' + @UserName + '] GRANT CREATE TABLE TO [' + @UserName + '] GRANT SELECT TO [' + @UserName + '] GRANT EXECUTE ON SCHEMA ::dbo TO [' + @UserName + '] /* Grant Database Roles to User */ ALTER ROLE [db_datareader] ADD MEMBER [' + @UserName + '] ALTER ROLE [db_datawriter] ADD MEMBER [' + @UserName + '] ALTER ROLE [rlBA_SQL_BizAgiWebApp] ADD MEMBER [' + @UserName + '] ALTER ROLE [rlBA_SQL_ExecuteBizAgiSPs] ADD MEMBER [' + @UserName + '] DECLARE @sSQL varchar(8000) SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''P'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE ON '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''P'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''TF'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT SELECT ON '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''TF'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''FN'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE on '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''FN'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sys.types WHERE is_user_defined = 1 AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE ON TYPE::'''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sys.types WHERE is_user_defined = 1'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 ') END ELSE PRINT ('ERROR!!!: Database ' + @DBName + ' Not Exist') |
| New user to existing DB Windows Authentication /* Create login for Bizagi Process Analyst*/ USE [master] GO /*change the following Variables with the user, Password and Database name before execution*/ DECLARE @UserName VARCHAR(120) = 'domain\process_analyst_user' --Write User name to conecct bizagi with the database i.e. domain\user DECLARE @DBName VARCHAR(120) = 'your_Bizagi_database' --Specify database name to assign user EXEC('CREATE LOGIN [' + @UserName + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master]') EXEC('ALTER LOGIN [' + @UserName + '] ENABLE') EXEC('CREATE USER [' + @UserName + '] FOR LOGIN [' + @UserName + ']') EXEC('GRANT ALTER SERVER STATE TO [' + @UserName + ']') /* Create user on Bizagi existent Database */ IF EXISTS (SELECT * FROM sys.databases WHERE name = @DBName ) BEGIN EXEC(' USE ' + @DBName + ' CREATE USER [' + @UserName + '] FROM LOGIN [' + @UserName + '] /* Grant permission on Database */ GRANT ALTER TO [' + @UserName + '] GRANT CREATE TABLE TO [' + @UserName + '] GRANT SELECT TO [' + @UserName + '] GRANT EXECUTE ON SCHEMA ::dbo TO [' + @UserName + '] /* Grant Database Roles to User */ ALTER ROLE [db_datareader] ADD MEMBER [' + @UserName + '] ALTER ROLE [db_datawriter] ADD MEMBER [' + @UserName + '] ALTER ROLE [rlBA_SQL_BizAgiWebApp] ADD MEMBER [' + @UserName + '] ALTER ROLE [rlBA_SQL_ExecuteBizAgiSPs] ADD MEMBER [' + @UserName + '] DECLARE @sSQL varchar(8000) SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''P'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE ON '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''P'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''TF'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT SELECT ON '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''TF'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''FN'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE on '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''FN'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sys.types WHERE is_user_defined = 1 AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE ON TYPE::'''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sys.types WHERE is_user_defined = 1'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 ') END ELSE PRINT ('ERROR!!!: Database ' + @DBName + ' Not Exist') |
Assign an existing process analyst user to an existing DB
For the next scripts consider:
•process_analyst_user: Should be replaced with the name of your login account.
•your_Bizagi_database: Should be replaced with the name of your Bizagi database.
(Click the link)
| Assign existing user to existing DB /* Create login for Bizagi Process Analyst*/ USE [master] GO /*change the following Variables with the user, Password and Database name before execution*/ DECLARE @UserName VARCHAR(120) = 'process_analyst_user' --Write User name to conecct bizagi with the database DECLARE @DBName VARCHAR(120) = 'your_Bizagi_database' --Specify database name to assign user /* Create user on Bizagi existent Database */ IF EXISTS (SELECT * FROM sys.databases WHERE name = @DBName ) BEGIN EXEC(' USE ' + @DBName + ' CREATE USER [' + @UserName + '] FROM LOGIN [' + @UserName + '] /* Grant permission on Database */ GRANT ALTER TO [' + @UserName + '] GRANT CREATE TABLE TO [' + @UserName + '] GRANT SELECT TO [' + @UserName + '] GRANT EXECUTE ON SCHEMA ::dbo TO [' + @UserName + '] /* Grant Database Roles to User */ ALTER ROLE [db_datareader] ADD MEMBER [' + @UserName + '] ALTER ROLE [db_datawriter] ADD MEMBER [' + @UserName + '] ALTER ROLE [rlBA_SQL_BizAgiWebApp] ADD MEMBER [' + @UserName + '] ALTER ROLE [rlBA_SQL_ExecuteBizAgiSPs] ADD MEMBER [' + @UserName + '] DECLARE @sSQL varchar(8000) SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''P'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE ON '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''P'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''TF'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT SELECT ON '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''TF'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sysobjects WHERE type IN (''''''''FN'''''''') AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE on '''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sysobjects WHERE type IN (''''FN'''')'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 SET @sSQL = ''SELECT '''' IF EXISTS(SELECT name FROM sys.types WHERE is_user_defined = 1 AND name = '''''''''''' + name + '''''''''''') GRANT EXECUTE ON TYPE::'''' + name + '''' to rlBA_SQL_ExecuteBizAgiSPs'''' AS SQLStatement FROM sys.types WHERE is_user_defined = 1'' EXEC spBA_Sync_ExecQueries @sSQL, 1, 0 ') END ELSE PRINT ('ERROR!!!: Database ' + @DBName + ' Not Exist') |
To verify the proper creation of this user, you may run:
use [master]
exec sp_helprotect NULL, 'process_analyst_user'
consider:
•process_analyst_user: Should be replaced with the name of your login account.
You should see the following information:
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.