Creating multiple SQL Logins for a single database

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 https://cp.Winhost.com
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]
GO

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.

 

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.

 

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