Unable to view tables in SSMS in Azure but can execute select

Question

Hello,

I am trying to grant a user sql server access in Azure but seems to having issues.

• user should have read only access.
• Must be able to view the tables /view/Programmability in SSMS
• Able to click on view the underlying script definition.
• Should NOT be able to create, Insert, delete, alter, modify..

This is what i did.

in Master DB

CREATE LOGIN test1 WITH PASSWORD = 'Test123%57';

CREATE USER test1 FROM LOGIN test1

In the actual DB

CREATE USER test1 FROM LOGIN test1 WITH DEFAULT_SCHEMA=dbo;

ALTER ROLE db_datareader ADD MEMBER test1 ;

Alter Role db_denydatawriter ADD Member test1 ;

GRANT SELECT TO test1

Grant VIEW DEFINITION To test1

After the above, the user can run any select query and get the results from any table (ex: select * from [PeopleTable])

But when trying to expand the Tables/Views/Programmbility

Answer 1

Hi @sam nick

There exists a specific situation where clients launch resource-intensive queries that take time and create locks, preventing other users from expanding the table list. 

Check this article for more details.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".