Unable to access table through application user

Question

I have a table master.t_user_daily_aggregation table in one DB. I have created/configured external table master.t_daily_daily_aggregation in different DB(Report DB). When i try to access this table in Report DB as a master user with schema 'master' I can able to see the records where as when I try to access the table as application without schema 'master' i am getting the below error.

with Schema: Select * from master.T_USERS_DAILY_AGGREGATION
without Schema: Select * from T_USERS_DAILY_AGGREGATION

Msg 46833, Level 16, State 2, Line 3
An error occurred while excecuting query on remote server:
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'T_USERS_DAILY_AGGREGATION'.

Note: The default schema for the application user is master. In other environment I can able to fetch records for application user. Only in QA env I am unable to fetch the records.

Please help me to resolve this issue.

Answer 1

The error would indicate that in the QA environment the default schema for the application user is something else than master. Try running:

ALTER USER applicationuser WITH DEFAULT_SCHEMA = master

I would suggest, though, that once you start working with other schemas than dbo, you should always prefix tables with the schema.