CODEWITHSUNDEEP
sql-serverazure
samuelbrody1249
samuelbrody1249

Reputation: 4767

How to create a read-only user for a particular schema

After creating a user via CREATE USER... I want to give it access to my database and schema. Here is the way that is working:

EXEC sp_addrolemember 'db_owner', 'tom'
GRANT SELECT ON schema::dbo TO tom;

However, I'm having trouble doing this with the stored procedure, as the following doesn't give 'enough' privileges:

GRANT CONNECT TO tom;
GRANT SELECT ON schema::dbo TO tom;

What's wrong with using the CONNECT? What else do I need to grant such that they'll have select-all privileges on that schema?

Update: it seems I have to give it a more rigorous 'select'. Here is what worked:

GRANT CONNECT to tom;
GRANT SELECT to tom;
DENY SELET ON schema::other_schema to tom;
GRANT SELET ON schema::dbo to tom;
GO

Why do I need to add in the GRANT SELECT to tom ?

Upvotes: 0

Views: 3990

Answers (1)

Joseph Xu
Joseph Xu

Reputation: 6043

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.

Upvotes: 1

Related Questions