How to revoke access to a schema for a specific user ?

Question

How to revoke access to a schema for a specific user ?

Thorsten Hock 0

I did create new schemas and they are all visible when I connect as admin to the Azure SQL database.

I use this query to revoke permissions for the user 'userReport':

-- Revoke any existing permissions on the tables
REVOKE SELECT, INSERT, UPDATE, DELTE ON SCHEMA::bronze TO [userReport];

I am getting this as a result and I don't know why, as I am admin and I can see the schemas:

Msg 15151, Level 16, State 1, Line 27
Cannot find the schema 'bronze', because it does not exist or you do not have permission.

Mallaiah Sangi 650 Reputation points Microsoft External Staff

2025-04-30T12:09:01.19+00:00
Hi Thorsten Hock,

Greeting!

As I understand that, you are unable to revoke access to a schema for a specific user.

In Azure SQL Database, you can revoke a user's access to a schema using the REVOKE statement. However, Azure SQL Database follows a different permission model. Here’s how you can do it:

Revoke privileges from the schema: sql REVOKE CONTROL ON SCHEMA::schema_name FROM user_name;

Revoke access to objects within the schema: If the user has explicit privileges on tables, views, or stored procedures within the schema, revoke them separately: sql REVOKE SELECT, INSERT, UPDATE, DELETE ON OBJECT::schema_name.table_name FROM user_name

Revoke membership from database roles (if applicable): sql ALTER ROLE db_owner DROP MEMBER user_name;

If the user was granted access via a database role (like db_datareader, db_datawriter, or custom roles), make sure to check and remove them from those roles as well.

I hope this helps. Do let us know if you any further queries. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Thorsten Hock 0 Reputation points

2025-04-30T12:52:28.2666667+00:00

DELTE was a spelling mistake - you're reight.

THe schemas I'm looking for are not showing up when I execute the query.

They did show up under Security > Schemas in the Azure database when I'm connected via SSMS.
Nandan Hegde 35,026 Reputation points MVP

2025-04-30T13:31:33.1466667+00:00

Are there any objects within that schema? And also, are you logging in via an account that is server admin or Db_owner while executing this query?

2 answers

Your answer

Mallaiah Sangi 650 Reputation points Microsoft External Staff

2025-04-30T12:09:01.19+00:00

Hi Thorsten Hock,

Greeting!

As I understand that, you are unable to revoke access to a schema for a specific user.

In Azure SQL Database, you can revoke a user's access to a schema using the REVOKE statement. However, Azure SQL Database follows a different permission model. Here’s how you can do it:

Revoke privileges from the schema: sql REVOKE CONTROL ON SCHEMA::schema_name FROM user_name;

Revoke access to objects within the schema: If the user has explicit privileges on tables, views, or stored procedures within the schema, revoke them separately: sql REVOKE SELECT, INSERT, UPDATE, DELETE ON OBJECT::schema_name.table_name FROM user_name

Revoke membership from database roles (if applicable): sql ALTER ROLE db_owner DROP MEMBER user_name;

If the user was granted access via a database role (like db_datareader, db_datawriter, or custom roles), make sure to check and remove them from those roles as well.

I hope this helps. Do let us know if you any further queries. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Thorsten Hock 0 Reputation points

2025-04-30T12:52:28.2666667+00:00

DELTE was a spelling mistake - you're reight.

THe schemas I'm looking for are not showing up when I execute the query.

They did show up under Security > Schemas in the Azure database when I'm connected via SSMS.
Nandan Hegde 35,026 Reputation points MVP

2025-04-30T13:31:33.1466667+00:00

Are there any objects within that schema? And also, are you logging in via an account that is server admin or Db_owner while executing this query?

Answer 1

Nandan Hegde 35,026 MVP

First of all the syntax seems to be wrong . The DELET keyword is not correct, make it as DELETE.

And as a step to debug, can you login via the admin account and execute Select * from sys.schemas and check whether that schema is visible in output

Answer 2

Thorsten Hock 0

Problem solved.

The SSMS lost connection to the SQL server. After reconnecting the old query window did work, but delivered these strange error messages.

A complete restart of the SSMS and a new query window did it ... now I'm able to query the schemas and to alter permissions for the user.

Thanks for your support !

Nandan Hegde 35,026 Reputation points MVP

2025-04-30T14:03:46.5433333+00:00

Glad it got resolved!! Seems to be some cache related intermittent issue
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Mallaiah Sangi 650 Reputation points Microsoft External Staff

2025-05-02T11:38:15.4133333+00:00

Hi Thorsten Hock

I am glad to hear that your issue has been resolved.

Your feedback is important so please take a moment to accept answers.

Share via

How to revoke access to a schema for a specific user ?

2 answers

Your answer