Set a SQL windows authentication user without the db owner role

Last Month
JorgeR
HOW TO'S

Summary

You can use Windows Authentication to connect to the Bizagi database. To create, update or make deployments, you need to set a db owner role in the database.
You can also set user roles for process developers, without needing db owner roles. To set a user without db owner role, you need to run a script.

Applies to

Bizagi 11.2.3 and above

What you need to do?

Follow the next steps in order to set a user without the db owner role:

1. Open your SQL server and login using a db owner role.

2. Start a new query a paste the following query:

/* 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

EXEC('CREATE LOGIN [' + @UserName + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master]')
EXEC('ALTER LOGIN [' + @UserName + '] ENABLE')
EXEC('CREATE USER [' + @UserName + '] FOR LOGIN [' + @UserName + ']')
EXEC('GRANT VIEW 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')

3. Define the the variables @userName as the user to access the Bizagi project without the db owner role and @database, the Bizagi Project database.

4. Run the script

Considerations

The user set without the db owner role has the following considerations

  • Cannot create new projects
  • Cannot update projects to new versions
  • Cannot make deplyoments
Rate this Article:

Details

Last Modified:Last Month
Last Modified By: JorgeR
Type: HOWTO
Level: Intermediate
Article not rated yet.
Article has been viewed 124 times.

Options