How to access multiple databases with a single SQL Login

Discussion in 'Tutorials' started by dmitri, Aug 27, 2010.

  1. dmitri

    dmitri DiscountASP.NET Staff

    If you have two or more database addons on the same hosting account and they are happen to be on the same SQL Server, you can create an additional SQL Login and configure it to have access to multiple databases. To find out if you have more than one database on the same SQL Server, visit User Manager in your control panel:

    For 2008R2 server
    For 2008 server
    For 2005 server

    All databases you own on that particular server will be listed under “Main SQL 2008 Database Logins” section. Let’s say you own two databases on sql2k801 SQL server with the following names:

    SQL2008_123456_green
    SQL2008_123456_orange

    Go to your user manager (see the links above) and create an additional SQL User. Let’s say you created an additional SQL login with a name magenta.

    Open Management Studio and login to sql2k801 server with SQL2008_123456_green_user login. Create a database user (let’s say user01) in your SQL2008_123456_green database and map it to magenta login. For the instructions on how to do that please see this KB article.

    Repeat the step above for SQL2008_123456_orange database.

    Now you should be able to access both of your databases with magenta login.

    When you login to the server with your additional sql login, you can see all databases on the server. Do not panic, this is a normal behavior of sql server. Just locate the databases you own and you should be able to access them. You cannot access databases of other customers and other customers cannot access your database. It is like in a multi-apartment building: all tenants can enter the building, but can have the access to their own apartments only.

    Last, but not the least: all SQL Logins are unique on the server. If you cannot create an additional login, someone else probably created it before you. Just try a different name.
     

Share This Page