Schema Related Issue

Question

Dears

Please assist if you can

Lets say we have database test where in we have two schemas dbo and testuser. We have two table dbo.sales and testuser.product.

Now I want the users under testuser schema should have only select permission on dbo schema however in testuser schema they should have full rights for ddl , dml and dcl

Would appreciated if some can answer me with step wise approach. 

Thanks Saurav

Answer 1

There is an incorrect assumption in your post: users do not belong to a schema. A user has a default schema, but that is something else. And you cannot assign rights to users from which default schema they have.

But apart from that, say that you have role testuser_role. Then you would do:

GRANT CONTROL ON SCHEMA::testuser TO testuser_role
GRANT SELECT ON SCHEMA::dbo TO testuser_role

---

Erland Sommarskog, SQL Server MVP, [e-mail]