topic Re: Why db_owner is necessary for Azure SQL database permission as target in Qlik Replicate

Why db_owner is necessary for Azure SQL database permission as target

Liam — Thu, 29 Feb 2024 02:53:49 GMT

For using Microsoft Azure SQL Database as a target, I checked the Qlik Replicate user guide that mention must have at least the db_owner user role on the Microsoft Azure SQL Database,
but we can not apply db_owner role for our project cause our company security control limitation, for this situation, do you have any suggestion if we can use lower user role than db_owner?
or can you give a strong advise that we must use db_owner user role for the Azure SQL Database as a target? thanks!
reference link: https://help.qlik.com/en-US/replicate/November2023/Content/Replicate/Main/Microsoft%20Azure%20SQL%20Database/azure_SQLServerDBTarget_Security.htm

Re: Why db_owner is necessary for Azure SQL database permission as target

john_wang — Thu, 29 Feb 2024 04:10:16 GMT

Hello @Liam ,

Welcome to Qlik Community forum and thanks for reaching out here!

Looks to me the operations on Azure SQL database and MS SQL Server target are similar, Qlik Replicate need the permissions on any table in the target database for below operations (at least):

1- DDL

CREATE TABLE/ DROP TABLE / TRUNCATE TABLE / ALTER TABLE / CREATE UNIQUE INDEX /CREATE INDEX/ SP_RENAME / CREATE SCHEMA

2- DML

SELECT/INSERT/UPDATE/DELETE/BCP LOAD

3- The specific database connect/use/read/write and all other needful permissions

That's all in my mind. The 'db_owner' role is a high-level database role that typically has full control over a specific database, Qlik Replicate is certified with the permission.

Hope this helps.

John.

Re: Why db_owner is necessary for Azure SQL database permission as target

Heinvandenheuvel — Thu, 29 Feb 2024 08:10:12 GMT

This is likely a documentation error. Db_owner is indeed required for MS SQL type spurce endpoints but should not be needed for target endpoints. Did you try without?

Re: Why db_owner is necessary for Azure SQL database permission as target

aarun_arasu — Thu, 29 Feb 2024 13:44:50 GMT

Hello @Liam ,

Well I would agree with @Heinvandenheuvel , you may try to create a custom role and provide them the DDL and DML privileges and test them once

Regards

Arun

Re: Why db_owner is necessary for Azure SQL database permission as target

Liam — Tue, 05 Mar 2024 10:12:48 GMT

Thanks all, I will test them.

Re: Why db_owner is necessary for Azure SQL database permission as target

Liam — Tue, 05 Mar 2024 10:14:40 GMT

if documentation error, please help escalate the case and update it if possible, thanks!