topic Re: Full Load Only Permissions for MSSQL Source in Qlik Replicate

Full Load Only Permissions for MSSQL Source

simonB2020 — Wed, 01 Jun 2022 09:16:15 GMT

The Replicate docs do not differentiate between Full Load & CDC configuration.
It simply sates that to use MSSQL as a source, Replicate needs to be a db_owner & sysAdmin

I am thinking that this really isn't necessary for Tasks that are only Full Load ?
Can it work with just a db_datareader ?

I tried with one of our generic existing DB roles that gives select on all tables, but getting error:

SYS-E-HTTPFAIL, Command get_table_list failed when getting the list..
SYS,GENERAL_EXCEPTION,Command get_table_list failed when getting the list.

Before I go back to the DBAs to take a look at configuration, would be good to know what the precise requirements are for Replicate.

Many Thanks

Re: Full Load Only Permissions for MSSQL Source

SwathiPulagam — Wed, 01 Jun 2022 12:40:10 GMT

Hi @simonB2020 ,

Here are the minimum required permissions to perform the Full load with SQL Server Source:

USE [master]
GO
CREATE LOGIN [test_fl] WITH PASSWORD=N'******', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [test_sql]
GO
CREATE USER [test_fl] FOR LOGIN [test_fl]
GO
USE [test_sql]
GO
ALTER ROLE [db_datareader] ADD MEMBER [test_fl]
GO
use [test_sql]
GO
GRANT VIEW DATABASE STATE TO [test_fl]
GO

Thanks,

Swathi

Re: Full Load Only Permissions for MSSQL Source

SwathiPulagam — Wed, 01 Jun 2022 13:24:07 GMT

Thank you @simonB2020 for accepting it as a solution.