Confusion between Login and User in SQL Server

There is often confusion between Login and User in SQL Server. What is the difference and what is the link? I hope to clear that out in this short video:

What you need to remember is:

  • Login is a server (instance) level entity and receives server-level permissions, like VIEW SERVER STATE, CREATE ANY DATABASE.
  • User is a database level entity and receives permissions on objects within that database. Like SELECT permission on certain table. Those permissions are preserved within database backup.
  • Database User is linked with server Login if they have the same SID number. SID numbers must “match”, name matching is irrelevant.

You can check SID number of database user and server login with this query:

If database user’s SID does not match the login you would like to connect it with, you can use this command to fix it:

It will update the database user’s SID, but also the name of the user to match login’s SID and name. If you want the user to have different name than login, then you must use NAME option:

If you move the database to a different server, make sure that second server has logins with the same SID as first server, and it will work out-of-the-box. Eg. by creating SQL Login on the second server with taking the SID from first server. Otherwise, you will have to use that ALTER USER command after every move/restore.

To create a server login with specific SID, one must use “SID” option of CREATE LOGIN:

If you want to copy not just the the SID but the password too to another server, you have to use revlogin or one of the scripts available, but that is for another topic.

“Windows” type of SQL Server Logins have the same SID already, if it is the same windows account, in the same domain.

Leave a Reply

Your email address will not be published. Required fields are marked *

*