1

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:

"Create a user that can only access the schema for database X, Y and Z and not the data"

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 "dbo" schema only but some have multiple

Thanks

0

You can grant "View Definition" 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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy