Accessing Multiple Schemas in SQL Server for Synced Records

Certified Associate Developer

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