Unable to view tables & views (Object Explorer in ssms) - synapse

Question

I have granted users access to db_datareader on a particular database on a dedicated SQL pool but the user is unable to see the folders (Tables & Views) in ssms object explorer.
NOTE: The user is able to query the tables when he knows the name.

I have even tried granting user with below roles but issue still persists -
GRANT ADMINISTER DATABASE BULK OPERATIONS TO newuser;
GRANT CONTROL ON DATABASE::AnalyticsDatawarehouse TO newuser;

Answer 1

Hello @Vivek Komarla Bhaskar ,

Welcome to the MS Q&A platform.

It looks like the new user doesn't have access to the database scoped permissions.

Can you please run the below command and see your DB level permissions.

   SELECT p2.name AS UserName,  
  STRING_AGG(p1.name, ', ') AS DatabaseRoleName  
  FROM sys.database_role_members AS m  
  RIGHT OUTER JOIN sys.database_principals AS p1  
   ON m.role_principal_id = p1.principal_id  
  LEFT OUTER JOIN sys.database_principals AS p2  
   ON m.member_principal_id = p2.principal_id  
  WHERE p2.name IS NOT NULL  
   AND p2.name NOT IN ('dbo')  
  GROUP BY p2.name  
  ORDER BY p2.name  

if you don't find the user and permissions, please follow the below steps to create user and permissions.

--Create user in the database  
CREATE USER [<******@domain.com>] FROM EXTERNAL PROVIDER;  
  
--Grant role to the user in the database  
EXEC sp_addrolemember 'db_owner', '<******@domain.com>';  

Please note: db_datareader and db_datawriter database roles can provide read/write permission when you do not want to give db_owner permissions. However, db_owner permission is necessary for Spark users to read and write directly from Spark into or from an SQL pool.

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators