Although the words logins and users are often used interchangeably, they are very different things in Microsoft SQL Server. There has always been confusion over logins and users, especially for most new SQL Server users. Here I’ll explain the difference between SQL Server logins and database users.

Part 1: Logins vs. Users

Logins are created at the server level, while users are created at the database level. In other words, a login allows you to connect to the SQL Server service (also called an instance), and permissions inside the database are granted to the database users, not the logins. The logins will be assigned to server roles (for example, serveradmin) and the database users will be assigned to roles within that database (eg. db_datareader, db_bckupoperator).

Logins must be mapped to a database user to connect to a database. If your login is not mapped to any database user, you can still connect to SQL Server instance using SQL Server Management Studio (SSMS), but you’re not allowed to access any objects in the database. To access any objects in the database, you must have a login that’s mapped to a user in the database, and that user must be granted appropriate rights in the database.

The database user does not have credentials of its own and relies on the server login for authentication. This means that there is no password associated with a database user. Below we’ll walk you through the steps of creating a new login and user, mapping a user to a login.

Part 2: Create A New Login

To create a SQL Server login, please follow these steps:

1. Open SQL Server Management Studio and connect to your SQL Server instance.
2. In the Object Explorer panel (located on the left side), expand the server where you want to create a new login. Expand the Security option, right-click on Logins and then select New Login.
3. In the Login – New dialogue that appears, enter a new login name. In this example, I create a new login named jack and choose SQL Server authentication. Uncheck the box labeled “User must change password at next login”.
4. Click OK to create a new SQL Server login.

Till now I haven’t set up User Mapping for the new login jack, so there is no database users mapped to this login currently. You can check out this at the Login Properties window by right-clicking the newly-created login and select Properties.

I can then use SQL Server Management Studio to connect to the SQL Server instance with this login. But when I try to expand the database test, I got the following error message due to lack of database permissions.

In order to get access to a specific database, we need to create a new database user and map it to the login jack.

Part 3: Create A New User

To create a database user, please follow these steps:

1. Open SQL Server Management Studio and connect to your SQL Server instance.
2. In the Object Explorer panel (located on the left side), Expand the database in which to create the new database user.
3. Expand Security and then Users. Right-click on Users and then select New User.
4. In the Database User – New dialogue that appears, select SQL user with login from the User type list, and enter a name for the new database user.In this example, I create a new database user named jack_user, and set jack as the Login name as I want to map this database user to the login jack I created previously.
5. Click OK to create the user.

Once the database user is created, check out the Login Properties for the login jack and you’ll find the database user jack_user is now mapped to the login.

At this time you can connect to the SQL Server service with the login jack, and expand the database test without permission issue.

That’s it! I hope this article would be helpful for you to understand the difference between a login and a user in SQL Server.

Comments Offon Difference Between SQL Server Logins and Database Users »

Posted in SQL Server, Tips & Tricks

Tags: create sql server login sql server create new user sql server login user mapping sql server login vs user

2 Methods to Start SQL Server in Single User Mode

October 26th, 2014 by Admin

What is single-user mode?

There are certain situations when user may have to start a SQL Server instance in single-user mode. For example, you might need to restore your master database from a backup in the event of a failure or damage, detach the database and kill all the connections. Both actions require starting an instance of SQL Server in single-user maintenance mode.

When a database is started in single-user mode, the following events occur:

• Any current connections to the database are dropped without warning
• Only one user can connect to the database
• The CHECKPOINT process is not started

I’m going to walk you through the process of putting your SQL Server database into single-user mode. This can be either done via SQL Server Configuration Manager by setting the startup parameters or through the Command Prompt.

Method 1: Start SQL Server in single-user mode through SQL Server Configuration Manager

Run SQL Server Configuration Manager. Choose SQL Server Services from the left panel and then right-click on desired SQL Server service that needs to run in single-user mode. Select Properties from the drop-down menu.

In SQL Server 2014 or 2012, click Startup Parameters tab. Type -m in the Specify a startup parameter box and then click Add.

If you’ve installed SQL Server 2008, 2005 or an earlier version, click Advanced tab. Append the ;-m to the end of the Startup Parameters box.

Click Apply to save your changes. Restart your SQL Server instance and it should then run in single-user mode.

Method 2: Start SQL Server in single-user mode through Command Prompt

First of all, you need to figure out the name of your SQL Server instance. Press Windows key + R to bring up the Run box. Type services.msc and press Enter to open the Services window.

Find your desired SQL Server service in the list, double click it. This will open the Properties dialog box for that service. Copy the service name listed there. In my example, the service name is MSSQL$SQLEXPRESS.

Open up a Command Prompt with administrative privileges and run the following command to stop the SQL Server service.
net stop service_name

The next step is to start the service in single-user mode. This is done by specifying /m parameter with net start command.

How to know if a running SQL Server instance is in single-user mode

When your SQL Server instance is started in single-user mode, it prevents multiple clients from connecting to the server at the same time. For example, if you’ve already connected to your DB with the SA account, and then try to login again through SQL Server Management Studio (SSMS), you’ll get the following error message:

Login failed for user”. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

To fix this issue, you need to remove -m from the startup parameters and restart your SQL Server instance, which will bring the database back to multi-user mode.