Difference Between Login and User in SQL Server

 

I would like to write this article because it is a topic that some friends who are interested in SQL Server are usually curious about. Before reading this article, I suggest you read my article “How To Create a Login On SQL Server(Manage Logins)“.

When we want to create a user in SQL Server, usually, we create a Login in the instance as follows.

We can grant the following privileges to this login.

1. We can grant select permission on the system objects at the instance level.(dmw,dmf)
2. We can grant any permision we want in any database on the instance.
3. We can grant a “server level role” privilege at the Intance level(You can read my article “Fixed Server Level Roles in SQL Server”).

So what is User?

A login can be authorized in more than one database on an instance. I mentioned how to authorize a login on the database in my article “How To Create a Login On SQL Server(Manage Logins)“.

When you map a login to the database, sql server creates a user with the same SID(special identity for login and user) as this login under the database and executes the authorization operations in the database through this user-login relationship.

Sometimes when we want to map(granting public privileged in the database) the login with a database, we receive an error as follows.

User, group, or role ” already exists in the current database.

The reason for this error is;

Login wants to create a user with the same name in the database. We receive this error when such a user already exists in the database. In such a case, we can resolve the problem by deleting the related user from the security tab of the database as below and repeating the authorization process.

Or we can do this with the help of the following script. The script is valid for SQL Server 2008 and later versions. If you execute this script, the SIDs of the login and the user will become the same.

Before SQL Server 2008, we were doing this with the following script.

Sometimes you can connect to the database directly by creating a user without a login. It is possible to do this with contained databases. It is possible to convert existing databases to a Contained database without any interruption. I recommend that you convert the production databases to contained databases. Especially if you have more than one database on an instance. You can find details in my article “What is Contained Database“.

I suggest you read the article “How to Move Logins to Contained Database” to convert your existing Login to the Contained User without interrupting the application.

To create a user without a login, I suggest you read my article “How To Create SQL User Without Login”