I'm database novice and would like to get confirmation or correction on my understanding of the difference between a user under a sql server instance vs a user in a sql server database. For example, a user can be added under Instance-->Security--Login and a user can be added under Instance-->Databases-->Database Name-->Security-->Users.
Is the difference that the login under the instance is used for logging into the database via management studio and the user under the database is for database access? Also, is one dependent on the other? So if I want to add a user to the database, they have to be added to the instance first?
Thanks again, so the user can be a random account and only resides in the database?
Here's the scenario, I have a group of domain users that need read access to a database using windows authentication on a 3rd party software on the front end, not management studio. I have a domain group created with all the users in it. So if I understand correctly, I need to create a new login with the domain group account and then create a random user in the database and map it to the login?
Also, can the user be the same name as the login? Can it be a domain account?
The names can be the same all the way through, but you can not deny access for management studio (or MS Access, or Excel, etc.), without getting into a bit of coding. Even then, it is hit or miss. Access to a database (really any server resource) is either granted or not, there is no conditional bit on what programs the user can or can not use.