Access to multiple databases with same user login

Discussion in 'Databases' started by InApp, Apr 5, 2012.

Thread Status:
Not open for further replies.
  1. Hi,

    I have two databases created at the WinHost control panel.
    DB_12345_corporate with user as DB12345_corporate_user
    and DB_12345_module with user as DB12345_module_user

    1. First issue is when i login with respective user accounts, i can see the DB_12345_corporate in object explorer but i cannot see DB_12345_module when i connect using DB12345_module_user.

    2. second issue is i have views in DB_12345_corporate database which i call in stored procedures from DB_12345_module database. But since the user login are different for the two databases this is not possible.

    Hence i tried to map DB12345_module_user to DB_12345_corporate database.I followed the steps as below

    1. Login to the SQL Server instance with “ DB12345_corporate_user” using Management Studio
    2. In Object Explorer, navigate to Databases --> DB12345_corporate --> Security
    3. Right-click on Users folder and select “New”
    4. In “User Name” and “Login Name” enter “DB12345_module_user”
    5. Under “Database role membership” check “db_datareader” check-box


    3. When you connect to the server with “DB12345_module_user" sql login, it will connect to “DB12345_module” database because it is its default database by default.

    But i cannot see the db in object explorer.i tried change the default database for this sql login,

    ALTER LOGIN [DB12345_module_user]
    WITH DEFAULT_DATABASE = [DB12345_corporate]
    GO

    logged out of the server and login to it again with DB12345_module_user

    4. opened a new query window and executed the below

    select db_name()

    I could see DB_12345_module instead of DB_12345_corporate.

    can anyone advise how to fix this?
    Thanks
    Ramya
     
  2. Elshadriel

    Elshadriel WinHost Staff

Thread Status:
Not open for further replies.

Share This Page