Azure Synapse Analytics Serverless - cross database queries

Question

Synapse Analytics Serverless. Scenario: a ViewB in DatabaseB points to ViewA in DatabaseA where same user is created on both databases on order to read ViewB data

Question is how we can hide user access on DatabaseA so only ViewB is reachable but DatabaseA is not reachable or just "hide" from user access.

Answer 1

After you create ViewB in DatabaseB, which points to ViewA in DatabaseA, create the same user in both databases but limit the permissions like below :

• In DatabaseA, grant the user only the SELECT permission on ViewA.
• In DatabaseB, grant the user SELECT permission on ViewB.

Then you explicitly deny the user any other permissions on DatabaseA. This can be done using the DENY statement in SQL.

Create and Grant Permissions in DatabaseA

-- Create ViewA in DatabaseA

CREATE VIEW ViewA AS

SELECT * FROM SomeTable;

-- Create user in DatabaseA

CREATE USER [YourUser] FOR LOGIN [YourLogin];

-- Grant select permission on ViewA

GRANT SELECT ON OBJECT::dbo.ViewA TO [YourUser];

-- Deny all other permissions on DatabaseA

DENY CONNECT TO [YourUser];

Create and Grant Permissions in DatabaseB

-- Create ViewB in DatabaseB pointing to ViewA in DatabaseA

CREATE VIEW ViewB AS

SELECT * FROM DatabaseA.dbo.ViewA;

-- Create user in DatabaseB

CREATE USER [YourUser] FOR LOGIN [YourLogin];

-- Grant select permission on ViewB

GRANT SELECT ON OBJECT::dbo.ViewB TO [YourUser];