Product:

Microsoft SQL Azure database

Issue:

A new created SQL native user [Kalle] can not see the tables in the database, but he can login to SSMS.

Solution:

Please do not use the role db_datareader or db_datawriter or their deny equivalents. They are for backwards compatibility only.

Remove the user from the role with below command, did not help.

EXEC sp_droprolemember 'db_datareader', 'Kalle'

You have to drop the user and create him again;

DROP USER Kalle

DROP LOGIN Kalle

Use Master

CREATE LOGIN Kalle WITH PASSWORD = 'advancedpasswordhere'

CREATE USER Kalle FOR LOGIN Kalle
-- to be able to login from SSMS you need to have the user in master database --

CREATE USER Kalle FOR LOGIN Kalle

-- gives the user Kalle access to see all tables in the DM schema --

GRANT SELECT ON SCHEMA::DM TO Kalle

This should give that the user only have read access to all tables and views that are part of the DM schema in the database.

To list members of built in roles use:

 SELECT DP1.name AS DatabaseRoleName, 
isnull (DP2.name, 'No members') AS DatabaseUserName 
FROM sys.database_role_members AS DRM 
RIGHT OUTER JOIN sys.database_principals AS DP1 
ON DRM.role_principal_id = DP1.principal_id 
LEFT OUTER JOIN sys.database_principals AS DP2 
ON DRM.member_principal_id = DP2.principal_id 
WHERE DP1.type = 'R'
ORDER BY DP1.name;

To list if any user have DENY rights use:

SELECT l.name as grantee_name, p.state_desc, p.permission_name, o.name
FROM sys.database_permissions AS p JOIN sys.database_principals AS l 
ON p.grantee_principal_id = l.principal_id
JOIN sys.sysobjects O 
ON p.major_id = O.id 
WHERE p.state_desc ='DENY'

 

More information:

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-droprolemember-transact-sql?view=sql-server-ver16 

https://blog.sqlauthority.com/2017/03/02/sql-server-unable-see-tables-objects-ssms/ 

https://www.mssqltips.com/sqlservertip/2385/unable-to-see-a-sql-server-table/