I am confused with Login and User. I found following in articles : A "Login" grants the principal entry into the SERVER. A "User" grants a login entry into a single DATABASE. One "Login" can be associated with many users (one per database).
I can understand it theoretically. But, I think I might not have understood this practically.
I created a Login in my SQL server 2008 management studio by right clicking SERVERNAME=>SECURITY=>LOGIN. The default database was "master". Now, I can log into sql server with this login name and password. I noticed that if I change the default database in Login properties to a specific database, I cannot login again with this credential. I reverted back to "master" and it works. What happened here?
Also, why do we need users? I created a user by right clicking DATABASENAME=>SECURITY=>USERS. I cannot relogin with this user credential. So, what is the purpose we need this for. I can understand the theory of this answer but I need little more explanations to make sense.
Also, I am a .net developer, so I would like to know, what are the credentials provided in sql connection strings. Are they login or user or can be any of these?
The simplest explanation is that the SQL Server login gets you into the server, and the settings on that login control how it works in each database.
Don't worry about the database logins for the moment. You already went to SERVERNAME=>SECURITY=>LOGIN. Let's look at what you do with this login - right click and go into properties if you already created the login. Look under Roles - there are a number of different ones with different purposes on the server. But for an app, generally speaking, the average user should only have the Public role.
As far as the Database login, you go to the Mapping section to point your login to whichever databases it needs access to. When you map the login to the database, this CREATES the database login you saw under DATABASENAME=>SECURITY=>USERS if it does not already exist. The mapping is the most important part though, it's what literally gives the login the ability to see data in the database.
For applications, you're using the server login. If you set up the links to the databases you need in mapping, you don't really need to think about the database level login info.