locked
How to grant access for a login to only one database RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have a azure sql server with to databases, say database1 and database2 and a administrator login.

    With the administrator login, access to both databases is allowed. I want to add a login which can only access database2.

    I understand the difference between login and user. In my connection string to get access to the sql server, I can only set User and Password for the login, so I'm alway logged in with a Login, not by the defined user.

    So I have to add a login which only allows access to database2. Management Studio does not allow to manage logins. How can I add permission to the database2 for a new login?

    Sunday, November 8, 2015 8:24 AM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    You have two options:

    1) Create a new login, and a new user for that login in your database2. The login is for authentication (at the master database), and the user is for authorization (access to a particular user database). 

    -- in master db
CREATE LOGIN database2_login WITH PASSWORD = '{Some Strong Password}'
go

-- in database2
CREATE USER database2_user FOR LOGIN database2_login
go

    2) Create a contained user, which doesn't need a login and instead authenticates directly to database2.

    -- in database2
CREATE USER database2_containeduser WITH PASSWORD = '{Some Strong Password}'


    More information on users and logins here: https://azure.microsoft.com/en-us/documentation/articles/sql-database-manage-logins/

    Tuesday, November 10, 2015 5:43 PM