Full Load Only Permissions for MSSQL Source

[simonB2020]

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

[SwathiPulagam · Accepted solution]

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

[SwathiPulagam · Accepted solution]

Thank you @simonB2020  for accepting it as a solution.