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:
|
1 2 3 4 5 6 |
-- Server Logins SELECT sid, name, type_desc FROM sys.server_principals where [type]<>'R' ORDER BY name -- Database Users SELECT sid, name, type_desc FROM sys.database_principals where [type]<>'R' ORDER BY name |
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:
|
1 2 |
-- Fix database user's SID to match server Login's SID ALTER USER my_db_user WITH LOGIN=my_server_login |
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:
|
1 2 |
-- Fix database user's SID to match server Login's SID ALTER USER my_db_user WITH LOGIN=my_server_login, NAME=different_than_login |
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:
|
1 2 3 |
CREATE LOGIN [my_login] WITH PASSWORD = 0x0100B9B5BB9675F9E9507A602F78F03E750590C8A113EFB0EC1D HASHED, SID = 0xC73FD9FAB70D094EB887FFF6B823E4FC |
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