August 2, 2022 at 8:57 am
Hi Experts,
As per informatica they need SYSADMIN to collect data, is there any workaround not to give SA?
TIA
August 2, 2022 at 12:58 pm
start with the lowest permission they mention - and then give it a try, with a trace in place to see which commands it is trying to execute.
then grant permissions to more commands as needed until it has all required permissions.
trial and error until you get the correct set of permissions identified. And if you end up requiring a lot more than what you wish to give, but if it is still below sysadmin e.g. if it requires control server (nearly sysadmin) then you can grant it while denying some other privs.
August 2, 2022 at 9:38 pm
It clearly states that you do NOT need to grant sys_admin privs...
source connection must have the sysadmin role, OR have the db_owner role and the SELECT permission on the master.sys.fn_dblog function. Use the following SQL statements if you want the user to have the db_owner role:
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2022 at 10:36 pm
It clearly states that you do NOT need to grant sys_admin privs...
source connection must have the sysadmin role, OR have the db_owner role and the SELECT permission on the master.sys.fn_dblog function. Use the following SQL statements if you want the user to have the db_owner role:
yes but... "NOTE: For SQL Server on-premises instances, the user must have the sysadmin role." just below that quote above.
hence my suggestion.
August 4, 2022 at 12:29 pm
Thanks Federico & Jeff ,
As mentioned in the note on-premise need sysadmin , the initial load worked with minimum permission they mentioned but not the incremental. Threw below errro.
HTTP response status code is 500, Error while execute supplemental logging scripts for tables: [USE "TEST";EXECUTE sys.sp_cdc_enable_db; -- Error
org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [USE "TEST";EXECUTE sys.sp_cdc_enable_db;];
SQL state [HY000]; error code [22902]; [informatica][SQLServer JDBC Driver][SQLServer]Caller is not authorized to initiate the requested action.
Sysadmin privileges are required.; nested exception is java.sql.SQLException: [informatica][SQLServer JDBC Driver][SQLServer]
Caller is not authorized to initiate the requested action. Sysadmin privileges are required.,
USE "TEST";IF 0 = (SELECT is_tracked_by_cdc FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id AND s.name = N'dbo' WHERE t.name = N'Informatica')
EXECUTE sys.sp_cdc_enable_table
August 4, 2022 at 1:24 pm
try to enable cdc on database manually before kicking the process.
August 4, 2022 at 6:29 pm
Jeff Moden wrote:It clearly states that you do NOT need to grant sys_admin privs...
source connection must have the sysadmin role, OR have the db_owner role and the SELECT permission on the master.sys.fn_dblog function. Use the following SQL statements if you want the user to have the db_owner role:
yes but... "NOTE: For SQL Server on-premises instances, the user must have the sysadmin role." just below that quote above.
hence my suggestion.
Crud... not sure what's going on but the link in the original post no longer works for me in either FF or Edge.
Anyway, thank you for the correction.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2022 at 9:23 pm
Thanks, and, yes... it's there clear as day. Thanks Frederico.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply