Question

If you expand the “Security” folder on your SQL Server instance, you will see a “Login” folder. If you expand the “Security” folder on a database, you will see a “User” folder. What is the difference between a login and a user? Provide an example of a login and a user. Can a user exist without a login? Assertions need support with sufficient evidence.

Answer 1

here in the sql server user and login are different

logins are for authentication purpose and user are for authorization purpose i.e., login is authenticate whether he valid one or not and and users are used to give authorization to particular database

logins are created at server instance and users are created at database level.

suppose if you are new to project and you are going to be an sql developer. so sql admin will create a authentication for you to enter to the sqlserver called login. so when you enter with your credentials you will able to see all the databases in it. so when you try to open the database it will send error because you are not authorized to use that database.so if you have to work on the database for example xyz. then by right clicking on the database admin will create a db user and assign that user to the login associated with it. base on the permission of the user you can access different levels on the database.

i.e, if you don't have a permission on a particular table that table won't be accessed by you login. and a login can be mapped with different users(n database n users will be possible).

Users which are not associated with Logins will be called as Orphan ID's. You can either delete it from database level or you can create a login in SQL and map the orphan ID with the newly created ID.

This situation will araise when you restore a database from different server (i.e from server1 to server2) where server1 will the logins associated with the db user and when you restore in server2 these logins might not exist in server2 and hence it will be an orphan ID in server2 db.

if you have any doubt please comment below