How to prevent access to tables, views and stored procedures in an Azure SQL database?

Question

Can someone let me know how to go about preventing users (other than the creator) from being able to access or view certain tables, views, and stored procedures?

I'm currently using SSMS, but not sure which options to select:

I have updated this question to address the solution that @Bhavani provided below, as his suggestion didn't appear to work.

I added an Azure Active Directory user to the database as follows:

CREATE USER [[email protected]] 
FROM EXTERNAL PROVIDER 
WITH DEFAULT_SCHEMA = config;

ALTER ROLE db_datareader ADD MEMBER [[email protected]]; 

I then denied all access to a Table called dbo.UpdatedProducts as follows

Having completed the above, when [email protected] access the database he can still read,select,alter etc.. on the table dbo.UpdatedProducts.

Can someone let me know if I have missed out a step?

Updated permissions for [email protected]

-- Revoke permissions explicitly if previously granted
REVOKE SELECT, INSERT, UPDATE, DELETE ON dbo.UpdatedProducts TO [[email protected]];

-- Grant only the necessary permissions
--GRANT SELECT ON dbo.UpdatedProducts TO [[email protected]];
DENY SELECT ON dbo.UpdatedProducts TO [[email protected]];

With the above update, [email protected] is still able to select and on dbo.UpdatedProducts

Answer 1

Here is a step-by-step guide on how to prevent users (other than the creator) from accessing or viewing certain database objects:

• Right-click on the table, view, or stored procedure and select "Properties."
• Go to the "Permissions" tab.
• Click on "Search" to find the user or role to which you want to grant or deny permissions, as shown below:

• In the "Explicit permissions for [user/role]" section, select "Deny" or "Grant" for the specific permissions you want to restrict. For example, if you want to access the table, view, or stored procedure data, you can grant permission to select the option, as shown below:

When working with AAD users, it's important to note that permissions are managed through roles and memberships. Instead of using DENY permissions, consider using a combination of GRANT and REVOKE to manage permissions. Deny permissions can be tricky to work with and might not always behave as expected due to inheritance.

-- Revoke permissions explicitly if previously granted
REVOKE SELECT, INSERT, UPDATE, DELETE ON dbo.UpdatedProducts TO [[email protected]];

-- Grant only the necessary permissions
GRANT SELECT ON dbo.UpdatedProducts TO [[email protected]];

You have added the user to the db_datareader role, which has read access to all tables in the database. that is the reason for getting table after revoking, use below command to restrict access to the particular table:

DENY SELECT ON dbo.UpdatedProducts TO [[email protected]];