Query Error with "db_datareader" Role in tds_fdw

[zhangqj]

SQL Server's user 'A', assigned with 'db_datareader' role, encounters an error while executing a query through a user mapping to an external table in PostgreSQL. The error message returned is: "ERROR: DB-Library error: DB #: 262, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 14".

On the other hand, user 'B', assigned with 'db_owner' role, does not experience such issue and can successfully create a user mapping and query the external table.

Does tds_fdw not support read-only user queries? It seems only user with 'db_owner' role and above can successfully execute queries via tds_fdw.

Environment: PostgreSQL version: 12.2 tds_fdw version: 2.0.3 SQL Server version: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5

[navindex]

You also need to grant SELECT privileges on all schemas. The following read-only setup worked for me:

CREATE LOGIN my_user WITH PASSWORD = '<pwd>'; -- set password here
USE my_db;
CREATE USER my_user FOR LOGIN my_user;
ALTER ROLE db_datareader ADD MEMBER my_user;
GRANT SHOWPLAN TO my_user;

GRANT SELECT ON SCHEMA::[dbo] TO my_user;
GRANT SELECT ON SCHEMA::[my_schema] TO my_user;