Login and SQL Server User

Question

I am confused with Login and User. I found the following in the articles: "Login" provides the master entry in SERVER. "User" provides an entrance to enter one database. One "Login" can be associated with many users (one per database).

I can understand this theoretically. But, I think, I could not understand it practically.

I created Login in my SQL Server 2008 management studio by right-clicking SERVERNAME => SECURITY => LOGIN. The default database was "master". Now I can log in to the sql server with this name and password. I noticed that if I change the default database in the Login properties in a particular database, I cannot log into it again with these credentials. I returned to the "owner", and it works. What happened here?

Also, why do we need users? I created a user by right-clicking DATABASENAME => SECURITY => USERS. I can not correspond with these credentials. So, why do we need this. I can understand the theory of this answer, but I need a little more explanation to make sense.

Also, I am a .net developer, so I would like to know what the credentials provided in the sql connection strings are. Are they a username or user or can they be any of them?

Answer 1

The simplest explanation is that the SQL Server login takes you to the server, and the settings for this login determine how it works in each database.

Do not worry about database logins at the moment. You have already passed SERVERNAME => SECURITY => LOGIN. Let's see what you do with this input - right-click and go to properties if you have already created a login. Look in the "Roles" section - there are several different options on the server for different purposes. But for the application, generally speaking, the average user should have only a public role.

As for entering the database, go to the "Mapping" section to specify your username depending on which databases he needs to get. When you map the login to the database, it CREATES the database login that you saw in DATABASENAME => SECURITY => USERS, if it does not already exist. Display is the most important part, though, this is what literally gives the login the ability to see data in the database.

For applications, you use the server login. If you set up the database links that you need to map, you don’t need to think about the login data at the database level.

Answer 2

Logon exists only at the server level, so it is automatically mapped to the main database.

Users control access to individual databases. When you create a user, you can map it to a login (see Create a user in MSDN for syntax). If you create a user that is mapped to a database login, you can set it as the default and log in.

One of the reasons for this is to allow multi-user environments where many databases are located on one server, which not everyone who has access to the server should have access to. For example, suppose we provide the services of company A and company B, and we place the database for each on one server. We don’t want someone from company A (or, more importantly, someone who compromised the credentials of someone from company A) to be able to access data for company B, so we only create a user to log into system A is the database of company A. Here is a brief code setup for experimenting with:

-- This script assumes whoever is running it has sysadmin permissions on the instance of -- SQL Server on which it is running. Do not run this on a production instance. -- Create a database for each company on the server instance. create database CompanyA; create database CompanyB; go -- Create a login for each company on the server instance. -- SQL Server integrated security has it issues, but it useful for an example like this. create login CompanyA_Login with password = 'pa55wOrd1', default_database = CompanyA; create login CompanyB_Login with password = 'pa55wOrd2', default_database = CompanyB; go -- Create a user in the appropriate database for each login. -- We need to tell the server that we want to use a specific database use CompanyA; create user CompanyA_User for login CompanyA_Login; -- We're granting it dbo for the purposes of our example here; -- a broad permission set like that is a bad practice. alter role db_owner add member CompanyA_User; go -- Repeat the process... use CompanyB; create user CompanyB_User for login CompanyB_Login; alter role db_owner add member CompanyB_User; go -- Create a table in each database and populate it with some data. use CompanyA; create table dbo.sensitiveInformation ( sensitiveInformation NVARCHAR(50) NOT NULL ); insert dbo.sensitiveInformation (sensitiveInformation) values ('Oh man, it would be bad if this got out!'); go use CompanyB; create table dbo.sensitiveInformation ( sensitiveInformation NVARCHAR(50) NOT NULL ); insert dbo.sensitiveInformation (sensitiveInformation) values ('Oh man, it would be even worse if THIS got out!'); go -- Now, feel free to log in as either user and see what you can and can't do. -- You will find that the CompanyA_Login will never be able to access CompanyB's -- data and vice versa. This allows for secure multi-tenant environments. -- Once you're done playing around, we'll clean up our samples. use CompanyB; drop table dbo.sensitiveInformation; drop user CompanyB_User; go use CompanyA; drop table dbo.sensitiveInformation; drop user CompanyA_User; go use master; drop login CompanyB_Login; drop login CompanyA_Login; drop database CompanyB; drop database CompanyA; 

If you need separate, discrete security / permission sets, and you need one user to have more than one of these sets, you want to use database roles. This Tech Republic article gives a decent shine to the benefits of roles, although I would recommend checking out MSDN for the latest ways to create them.