how do you create a read-only user for a particular schema?

As we know, if we run EXEC sp_addrolemember 'db_owner', 'tom', the user will have the most privileges and can access all schemas of the current database.

I tried the following code, the user will only have the select privilege of the current database.

CREATE LOGIN bobby WITH PASSWORD = 'Password1'

CREATE USER bobby FOR LOGIN bobby;

GRANT CONNECT TO bobby;
GRANT SELECT ON schema::production TO bobby;

After I run this, I can access the specified database and the specified schema(production) with the user bobby.

enter image description here

Then I run GRANT SELECT to bobby;, I can see the user bobby can access all the schemas in the current db.

enter image description here

So we can conclude that:

  • GRANT SELECT to bobby; means grant all schemas to the user of the current database.
  • GRANT SELECT ON schema::production TO bobby; means grant specified schema to the user of the current database.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top