0

I have created an external table on my db with ADMIN user.

CREATE EXTERNAL TABLE ext.mytable
(...)
WITH (DATA_SOURCE = [DB in another server],SCHEMA_NAME = N'dbo',OBJECT_NAME = N'otherTable')

My external tables are in schema ext. Only administrator can see tables in ext schema.

Then I create a VIEW that SELECT rows from the external table:

CREATE VIEW live.MyView AS SELECT * FROM ext.mytable

My user own the schema live.

Then I granted SELECT permissions on that table to my user.

 GRANT SELECT ON ext.mytable TO user_service

Finally I connect to db with user_service and I try to query the table:

SELECT * FROM ext.mytable

But I get this error:

The SELECT permission was denied on the object 'mytable', database 'insights-dwh', schema 'ext'.

The table in datasource referenced by the external table own to another user in a different server. So the admin who created the external table is different from the table referenced.

What's wrong in granting permissions? How can I solve the problem?

Thank you

Simone
  • 2,304
  • 6
  • 30
  • 79
  • When you say "The table in datasource referenced by the external table own to another user in a different server" does that mean that `ext.mytable` is actually a view (materialized or not) to a different table on a different database? – Scratte May 01 '21 at 10:48
  • yes.. i have an EXTERNAL TABLE.... and then a VIEW with JOINS, conditions, ... etc on that table – Simone May 01 '21 at 14:33
  • You'll need to mention that on your Question. I'd probably mention the details on that external table. As in the select and perhaps the database link used. Are you able to log into "ext" as "user_service" and select from it? – Scratte May 01 '21 at 14:37
  • 1
    changed... I hope is clearer now... I cannot do a SELECT on the `ext` table.. and I do not see the table.. I want `user_service` cannot see the ext table but I would like he can select from it via a view – Simone May 01 '21 at 14:49
  • Can the user that you used to make the grant see the table? – Scratte May 01 '21 at 14:52
  • Yes... he is the same user who created the external table – Simone May 01 '21 at 15:11
  • yes, there is an external data source, but not a linked server... – Simone May 01 '21 at 15:29

0 Answers0