Schema Related Issue RRS feed

  • 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

    Saturday, March 9, 2013 9:38 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, esquel@sommarskog.se
    • Proposed as answer by SathyanarrayananS Saturday, March 9, 2013 10:51 AM
    • Marked as answer by Maggie Luo Tuesday, March 19, 2013 10:17 AM
    Saturday, March 9, 2013 10:10 AM