Accessing Schema by two users

  • Hi friends

    i have created two different schemas S1,S2 in my database, and i have two

    different users U1 and U2.now i have assigned schema S1 to User U1 its fine

    user is able to access all tables of S1 but now the problem i want user2 U2

    to access both S1,S2 (tables ) which i am unable do so.

    second thing while creating users there two different options like

    1)OWNED SCHEMAS

    2)DataBase role membership

    i have select options from both the panes. i am selecting only OwnedSchemas options but not Database role memberships

    please let the options

    thanks

  • you can grant on schema basis.

    Check bol !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Hello,

    You can grant User "U2" permissions in the Schema "S1" as well as S2.

    In SSMS Select the DB then Security, then Schemas, right click on the Schema and select Properties. Under the Permissions tab, click the Add button.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • Hi john

    thanks for your reply,

    now its solved ,but i just confused where actually roles and schemas are

    separately used .does this problemcan be achived by any means using Roles.

    Thanks

  • Hello,

    To be honest, I prefer to use DB Roles for this.

    You can create two DB Roles, assign whichever permissions you like to each one and then add the users to the appropriate Role. It is a good way of grouping permissions, and you can even add the same user to multiple DB Roles (which is what you would want to do for your user "U2").

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • Ok fine thanks for your replies

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply