I have multiple databases in Azure Synapse Analytics lake database. Now I am trying to limit the read access to one of the users. I was using the following script to create the login for the user:
Use master CREATE LOGIN [[email protected]] FROM EXTERNAL PROVIDER; --Create user in your database. Change context to your database.
Use MyDB -- In your database CREATE USER Alias FROM LOGIN [[email protected]]; --Add user as a member of the specified role in your database.
ALTER ROLE db_datareader ADD member Alias;
After running this script, the user can connect to the Azure Synpase Analytics workspace serverless sql enpoint using SSMS but the user can still see all the databases, even though he can only access one database (MyDB above). Is there any way to only allow him to see MyDB above?
I have also tried to deny view to public by using: use [master] GO deny VIEW ANY DATABASE TO [public] GO
After that, he is no longer able to login. Now I tried to revert the deny back by using the following script:
use master go grant view any database to public
use [master]
GO
GRANT VIEW Any database TO public
GO
He is still not able to login. I also tried
use [master]
GO
GRANT VIEW Any database TO [[email protected]]
GO
Still no luck. How he is getting network deny error. Any ideas how to revert the deny view to public back in lake database?
Any help is appreciated!
You don't need to create a login for the user in the master database. You just need to create a contained database user on the specific database you want to give him access.
USE [YourDatabase];
CREATE USER [YourAzureEntraUser] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_datareader ADD MEMBER [YourAzureEntraUser];
GO
The user will be able to connect only if he specifies that database when trying a connection, He won't be able to list other databases and he won't be able connect to the master database either.