Creating multiple SQL Logins for a single database

Discussion in 'Databases' started by rum, Jan 13, 2011.

  1. rum

    rum Winhost Staff

    When you create a database on Winhost, you are provided with a single SQL Login that has dbo rights on this database. Creating additional SQL Logins for a single database is not directly possible. However, there is a work-around solution for those who have Winhost max or ultimate plans. If you have max or ultimate plans, you can create 5 or 10 databases respectively. When you create multiple databases on a site, you also create multiple SQL Logins on the same instance of SQL Server. This provides you the ability to grant access for your database to those additional SQL Logins and control their permissions.

    As an example, let us create a secondary sql login, and grant it only read permissions on our primary database. During this tutorial, you will need to login to the instance of SQL Server several times with several sql logins. Although it is possible to stay logged in with several logins simultaneously in Management Studio, it can get very confusing, so make sure you are logged in to the server only with a single login.

    First of all, let's create our primary and secondary databases in Winhost control panel.

    1. Login to your Winhost Control Panel at
    2. In the navigation menu under Winhost logo click on "Sites" tab
    3. In "Your Sites" section click "Manage" link next to a site you will be creating the databases for
    4. In the "Site Tools" section click on "MS SQL 2008" button
    5. Click on "Add" button and enter the word “primary” in the text field next to the “Database Name” label
    6. Click “Create” button
    7. Repeat the above steps, and enter the word “secondary” in the step number 5

    You now will have two databases: “DB_12345_primary” and “DB_12345_secondary” with the corresponding SQL Logins: “DB_12345_primary_user” and “DB_12345_secondary_user”

    Where 12345 is your site ID, which will be different on your end.

    1. Login to your SQL Server instance with “DB_12345_primary_user” using Management Studio
    2. In Object Explorer, navigate to Databases --> DB_12345_primary --> Security
    3. Right-click on Users folder and select “New”
    4. In “User Name” and “Login Name” enter “DB_12345_secondary_user” It is perfectly fine to have the same name for both: the sql login and the database user it is mapped to
    5. Under “Database role membership” check “db_datareader” check-box
    6. Hit OK button to finish creating a database user and mapping it to the secondary sql login.
    7. Log out of the server by right-clicking on the sql server instance in object explorer and selecting “Disconnect”

    Your “DB_16104_secondary_user” will now be able to query your “DB_12345_primary” database. When you connect to the server with “DB_12345_secondary_user” sql login, it will connect to “DB_12345_secondary” database because it is its default database by default. If you want to change the default database for this sql login, please login to the server with this login and run the following query

    ALTER LOGIN [DB_12345_secondary_user]
    WITH DEFAULT_DATABASE = [DB_12345_primary]

    Now logout of the server and login to it again with DB_12345_secondary_user. Press “new query” button or hit Ctr+N to open a new query window and run the following statement:

    select db_name()

    That will tell you which database you are connected to. You should now see DB_12345_primary in the query result window. You will not be able to see the primary database in object explorer while connected to it with secondary login, and this is the only inconvenience. You should be able to do everything with T-SQL code, however. You can grant or deny any permissions to your secondary sql login, but it is beyond the scope of this tutorial.
    Last edited by a moderator: Oct 14, 2015
  2. Nice work.
  3. I've tried this but under point 5
    5. Under “Database role membership” check “db_datareader” check-box
    i can't find “db_datareader" and I got the error

    Create failed for Login 'test'. (Microsoft.SqlServer.Smo)
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)

    Anny Idea what I'm doing wrong?

  4. Ray


    I think you maybe going to the wrong place. Send us a large and clear screenshot of what you are seeing.
  5. Thanks for teh reply you 'we right Is was looking at the wrong place. I was looking at the server securty instead of the database securty. It seems to work fine now. I guess now I've to go looking for the smartes way to grand permissions on teh different databases. I must say it's a bit convusing that not all the db are shown in the object explorer but I guess I'll just open Managment studio serveral times.

    I guess I'll keep playing a bit with schemas and roles to figure out what the easiest way is to grand permisions for each database.

    I still have 1 question to check if I've understand the post entirely:

    Is this correct?

    In the example we have a DB_12345_primary_user he is dbo of database DB_12345_primary if I whant I can add rights to DB_12345_secondary_user by a role member so if I choose db_ower both logins have the same permisions.
    But due to the server configuration it's not possible that on the database DB_12345_primary the user DB_12345_secondary_user becomes the ower and the DB_12345_primary_user has limited rights on this db. In other words DB_12345_primary_user will always be the dbo.

    Martijn Carol
  6. Ray


    No, not quite correct. For each database you can set multiple DBO's to one database. Therefore for database DB_12345_primary you can have both DB_12345_primary_user and DB_12345_secondary_user as a dbo to DB_12345_primary
  7. rum

    rum Winhost Staff

    Every database has one dbo user, which by default is assigned to the db_owner database fixed role. You can remove db_owner role from dbo user of your DB_12345_primary database, therefore limiting your DB_12345_primary_user login permissions. I strongly advise against doing so as you will not be able to restore those setting by yourself.
  8. Thanks a lot I thoink I understand
  9. Is the only way to create 2 users (1 owner with update and 1 read only user) is to have two database? My hosting plan only allow me to have 1 database. Is the user 'guest' something useable?
  10. I tried creating a secondary database but got a permission error when I try to grant access to secondary user from primary using Management Studio. Any ideas?
  11. Ray


    Check to see if the databases exists on the same SQL server or different SQL servers. If they exists on different SQL servers you will not be able to grant permissions to a DB user that exists on a different server.
  12. Hi Ray, that was indeed the problem. The secondary database was created with SQL 2008 R2 and the first one is still SQL2008 and therefore on different servers. Thanks!
  13. I tried the steps: 1) creating a new database db_123_secondary 2) logged in to db_123_primary using db_123_primary user and it would not let me add user db_123_secondary.
  14. junior

    junior Winhost Staff

    What is the error that you are getting? please recall to Check to see if the databases exist on the same SQL server or different SQL servers. If they exist on different SQL servers you will not be able to grant permissions to a DB user that exists on a different server

Share This Page