Saturday, March 09, 2013 9:38 AM
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.
Saturday, March 09, 2013 10:10 AM
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, firstname.lastname@example.org