One concept that often confuses newcomers to SQL Server is the difference between a login and a user.
In an earlier post, I mentioned that being authenticated to a server is what's called a login. In this example shown, I have a log in here called Malathi. But as I said, this means nothing about what databases Malathi then has access to.
The connection between a login and a database is what the concept of a user is about.
It may seem a little strange at first that a login has a name, yet in each database, that same login could have a different user name. But a database user is a sort of a mapping that says that login is called this name.
Malathi is the login still but it was authenticated as that and in this first database is the user dbo (which is actually short for database owner). In the next databases, Malathi has the username Mala and is treated as that user throughout that database.
Now obviously it's far simpler if the user name is the same name as the login. And that's the situation with the last database shown.
Now there are 3 common types of users: the first is a log in who's been granted access to a database. That's the same as we've shown in this diagram.You can also have a Windows group that's been given access so for example, we might have in Windows, a SalesPeople group and that group could be given access to a database. Then if Tom is a salesperson, Tom would have access to that database because of his Windows identity and group membership.
The final option is that since SQL Server 2012, we can also have a database that does the authentication instead of the server. That's a very special case, called a contained database. It's not very common. But what it does is it allows the user to connect to one database and have no access to anything else on the server.
Learning T-SQL
It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.