Historically, we’ve used Data Sources and Custom Data Types (CDTs) to manage our data integrations. As we transition to using synced records for our new application, we’ve encountered a challenge related to schema access in SQL Server.
Currently, our SQL Server Data Source is configured with a username that has access to the database. However, any synced record created under this data source only has access to tables within the default schema. Our data model spans multiple schemas within the same database, and ideally, we need to connect tables across these schemas within a single synced record.
One workaround is to create separate data sources for each schema, each using a different username. However, this would require maintaining multiple data sources for the same database, which adds unnecessary complexity and overhead.
We’re seeking guidance on the best way to configure our SQL Server connection so that synced records can access tables across multiple schemas within the same database, while maintaining a single data source.
We would greatly appreciate any recommendations or best practices to help us achieve this.
Discussion posts and replies are publicly visible
As far as I know, this is not supported.
With synced records, you could follow the same approach as you had with data sources. And Appian does not care from where a synced record is coming from. It all becomes one large data model.
Only 2 options
1. Grant access to all schemas for a single user
2. Create separate datasources and link the Record Types
#2 doesn't seem very complicated, how many data sources would you have?
Thank you, Stefan and Mathieu. We have decided to go with one unique SQL user for each schema name