locked
Can I have a Login that uses a Security Group and per Database restrictions by User? RRS feed

  • Question

  • I have a database server that has many different databases on it.  We may have 50 databases on the server, one database for each client.  I would like to know if it is possible to create a single login to the server using a Windows Security Group, while still restricting the Database's to a Client by Client login.

    For example, I will create the following:

    Security Group: Clients

    User: Client 1 (Member of Clients Security Group)

    User: Client 2 (Member of Clients Security Group)

    I know I can create a login using <Domain>\Clients so that all users in the Clients Security Group can log in to the database.

    My question is, can I then restrict each database by the User without having to have a Login for the user?  I.E. Can I have one Login for <Domain>\Clients and under each database restrict access to <Domain>\Client 1 or <Domain>\Client 2?

    When I work on setting up the User Mappings, the only users I can select are those that have Logins, so I am not seeing a way to do this, but I am hoping I am missing something.

    Thank you in advance

    Ben

    Thursday, December 4, 2014 12:06 AM

Answers

  • In differece to skshakya, I believe that you should be able to do what you ask for.

    CREATE LOGIN [Domain\Group] FROM WINDOWS
    go
    USE Database1
    go
    CREATE USER [Domain\Client1]
    go
    USE Database2
    go
    CREATE USER [Domain\Client2]
    go
    ...

    However, I am not entirely sure that it is a good idea. I can't really say what is wrong, but I get an uneasy feeling.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Ben Haynie Friday, December 5, 2014 4:01 AM
    Thursday, December 4, 2014 10:18 PM

All replies

  • Hi Ben,

    You can only add Logins to database which are exists on server if not then you will not be able to do the same.

    Best way to achieve this by adding Two logins. one for group Domain\Client1 and another for Domain\Client2.

    After adding logins to server, you can map logins to their databases.

    Please Mark This As Answer if it solved your issue 
    Please Mark This As Helpful if it helps to solve your issue

    _____________________________________

    Thanks,

    Shashikant

    Thursday, December 4, 2014 1:11 AM
  • Ben

    Which version of SQL Server?  Have you looked at Contained Databases to see if this option would help you?

    Thursday, December 4, 2014 1:22 AM
  • In differece to skshakya, I believe that you should be able to do what you ask for.

    CREATE LOGIN [Domain\Group] FROM WINDOWS
    go
    USE Database1
    go
    CREATE USER [Domain\Client1]
    go
    USE Database2
    go
    CREATE USER [Domain\Client2]
    go
    ...

    However, I am not entirely sure that it is a good idea. I can't really say what is wrong, but I get an uneasy feeling.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Ben Haynie Friday, December 5, 2014 4:01 AM
    Thursday, December 4, 2014 10:18 PM
  • Thank you.  I know what you mean, something feels off and I can't put my finger on it.  Sorry, I hadn't considered setting up the user manually.  I guess you don't need to set it up for a login.  Thank you for the pointer!
    Friday, December 5, 2014 4:02 AM