SQL SERVER – Difference Between Login and User – SQL in Sixty Seconds #070

“What is the difference between Login and User in SQL Server?”

This is a very common question I often receive. Yesterday when I was browsing Facebook, I once again noticed this question once again asked in SQLBangalore group. My very good friends – Vinod Kumar and Balmukund Lakhani had already answered the question there. However, I every time, I read this question, I realize that not everyone knows the basics of these two concepts. If I have to explain the difference between them, it may take a long time, but I will try to explain it at a very basic level.

Basic Explanation:

SQL Server Login is for Authentication and SQL Server User is for Authorization. Authentication can decide if we have permissions to access the server or not and Authorization decides what are different operations we can do in a database. Login are created at the SQL Server instance level and User is created at SQL Server database level. We can have multiple user from different database connected to a single login to a server.

I hope this is clear enough. If not, I strongly suggest you watch following quick video where I explain this concept in extremely simple words.

Action Item

Here are the blog posts I have previously written on the subject of security. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

Reference: Pinal Dave (http://blog.sqlauthority.com)

9 thoughts on “SQL SERVER – Difference Between Login and User – SQL in Sixty Seconds #070

  1. This is useful and simple, Pinal. I didn’t realize that I didn’t know this. I’ve always just used a script to create both a login and a user for what I needed and never really thought about the distinction. Your simple description of authentication and authorization is valuable.


  2. I have DB1.tableA , & StoreprocA( this one contains update DB1.tableA & update DB2.tableB,update DB2.tableC)
    have userA, UserB – with db_owner for DB1 & DB2

    my requirment is: userA only update the tableA not userB ( through SQL statement and Storeprocedure)

    I pass below command
    (deny update on DB1.dbo.tableA to UserB)
    (deny execute on object::DB1.dbo.StoreprocA)

    output: can’t update through SQL statement but storeprocedure still updates.


  3. “We can have multiple user from different database connected to a single login to a server.”

    I think multipe logins to the server can have one user to the database.
    Example : login 1 / login 2 / login 3 associated to a user (DBA) having such role and permissions on such or such DB.

    Correct me if I am wrong


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s