Why db_owner is necessary for Azure SQL database permission as target

[Liam]

    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%20...

[john_wang · Accepted solution]

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.

[Heinvandenheuvel]

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? 

[aarun_arasu]

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

[Liam]

Thanks all, I will test them.

[Liam]

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