none
SQL Server Permissions Issue RRS feed

  • Question

  • Hello All,

    I have 2 Schema in my SQL Server also i have 2 logins i have mapped the login with schema respectively.

    for eg.

    Login1 is mapped with schema1 and login2 mapped with schema2 and if i will login with login1 then i should see the list of views owned by schema1 in SSMS(expand views in SSMS) and if i will login in with login2 then i should see the views of schema2 in SSMS(expand views in SSMS).

    Thanks 

    ChetanV

    Thursday, February 14, 2019 2:50 PM

Answers

  • As Brenda suggests, your question is not fully clear. One problem is that you don't map login to schemas, but to users. Nor do schemas own views, but users do.

    But this script describes the commands you would run:

    CREATE LOGIN login1 WITH PASSWORD = 'who needs passwords anyway?'
    CREATE USER login1
    go
    CREATE SCHEMA login1
    go
    GRANT SELECT on SCHEMA::login1 TO login1


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, February 14, 2019 10:17 PM

All replies

  • What is your question or issue?
    Thursday, February 14, 2019 4:40 PM
  • As Brenda suggests, your question is not fully clear. One problem is that you don't map login to schemas, but to users. Nor do schemas own views, but users do.

    But this script describes the commands you would run:

    CREATE LOGIN login1 WITH PASSWORD = 'who needs passwords anyway?'
    CREATE USER login1
    go
    CREATE SCHEMA login1
    go
    GRANT SELECT on SCHEMA::login1 TO login1


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, February 14, 2019 10:17 PM

  • Login1 is mapped with schema1 and login2 mapped with schema2 and if i will login with login1 then i should see the list of views owned by schema1 in SSMS(expand views in SSMS) and if i will login in with login2 then i should see the views of schema2 in SSMS(expand views in SSMS).

    Why not make the two DB users owners of their respective schemas so that one user cannot see the other schema?

    However, note that if the views under schema1 reference tables owned by schema 2, you'd need to explicitly grant user1 'select' against the tables owned by schema 2 (user2's schema). That way, user1 can query the views (under schema1) while not being able to see the schema 2 (of course, it can still see the objects explicitly being granted access upon).


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Thursday, February 14, 2019 11:49 PM
  •  Nor do schemas own views, but users do.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Erland, unless I am missing your point, shouldn't it be -- Nor do users own views, but schemas do?


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Thursday, February 14, 2019 11:52 PM
  • Erland, unless I am missing your point, shouldn't it be -- Nor do users own views, but schemas do?

    Views - and other objects - are owned by principals, that is users or roles. Schemas are not principals. But a view and other schemabound objects belongs to a view or are part of a schema. Of course, in casual speech, you could say that a schema owns the objects that belongs to the schema. But since ownership has a precise and exact meaning in SQL Server that usage is not very fortunate.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, February 15, 2019 8:57 AM
  • Hi Chetan Vishwakarma,

     

    Could you please tell us your specific question?

     

    Based on my test, I create a login and map it to user( test02).When I configure a schema(Chains) for test02, I used this login to connect to SQL server, and all I could see was the view below Chains.

     

     

     

     

    For more details, please refer to https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-login?view=sql-server-2017#user-mapping

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, February 15, 2019 9:10 AM