MS SQL - User access to schema not data I look after some infrastructure that includes some MS SQL Servers (SQL Server 2017). I am by no means a SQL expert and have been asked the following:
&quot;Create a user that can only access the schema for database X, Y and Z and not the data&quot;
I am able to create a user that can only access X, Y and Z DB and not A, B, C etc. But I cannot work out have to restrict it to viewing the schema only. Can anyone provide any assistance?
Most of the DBs in question use the &quot;dbo&quot; schema only but some have multiple
Thanks
You can grant &quot;View Definition&quot; permissions at the schema level in most SQL databases. I would create a new role in the database, for example schemareader, and assign permission like this:
GRANT SELECT, VIEW DEFINITION ON SCHEMA::[dbo] TO schemareader;