February 27, 2009 at 12:35 pm
I am new to SQL Server 2005...and was reviewing the following msdn url for creating logins and users: msdn.microsoft.com/en-us/library/aa337552.aspx
Can someone explain what the difference is between a db login and a db user?
From my understanding, you use the db login within SQL Server manager, or with your .NET code, correct? Then, you create a user and associate it to a login? Why? What's the relevance of knowing or having a user associated to a login, when the login is sufficient alone? Would I use the user logging into sql server mgr or within my .net code?
Do I need to associate a login with a user?
thanks!
February 27, 2009 at 2:41 pm
Might I suggest you start / extend your education abut SQL Server 2005 by using the Search box at the top of this page and search on "Security". I am sure you will then be able to answer your own question plus gain a great deal of knowledge on why you would want to use logins, users, roles and a great deal more.
February 28, 2009 at 1:37 pm
I understand that the login is for the db server, but when associating a user to a login does what? Part of my understanding is to merely associate the user to a default db.
My question is a little more than the 101 level and referring to the Search section isn't the solution. I'm looking for more of a business reason to understand what the benefit is of associating the db server login to a user within a db...and how that user will be reflected in .net code.
February 28, 2009 at 3:22 pm
In SQL Server terms:
Login means you can connect to the SQL Server. It does NOT mean you have access to any of the user databases. User means you can connect to the database once you're connected to SQL Server. The confusion here is that Windows user is not the same as database user. A Windows user is a login.
Here's a way to think of it. Imagine an apartment building where there's an electronic pad to enter at the front door. The apartment building represents SQL Server. If you know the code, you can enter the building. This is a login. A Windows user or a Windows security group is a login. But once you've entered the building, that doesn't mean you can get into any of the apartments. For that you need a key. An apartment represents a database. If you have the right key, you are a user for that database.
K. Brian Kelley
@kbriankelley
March 1, 2009 at 11:51 am
Great...that helps alot! So if I have a 'manager_login', and associated with a 'JohnDoe_user' db user, should I use JohnDoe_user within my Asp.net code? My guess is yes, since the manager_login like you described has access to the db server, but not a particular db.
Thanks!
March 1, 2009 at 1:39 pm
No, you want a manager_login within your application. Remember, the application is what is trying to get into the SQL Server. Once it connects, SQL Server will handle the mapping between the login and the user.
Also, it is typical to ensure the user pretty much matches the login. In other words, if you're coming in as manager_login, the user name within the database would be manager_login as well. It makes it easier from a security perspective because you can glance at it and know what's going on.
K. Brian Kelley
@kbriankelley
March 2, 2009 at 8:01 am
So always use the db_login for asp.net code....not the actual db_user. Got it.
Ahhh, makes sense. Have a 1:1 relationship for a db_user and a db_login. So if I hypothetically had 3 managers that require read-only access, I wouldn't create 1 db_login called "read_login" and associate it to 3 different users? Sounds like simply create 3 different db_users and 3 db_logins!
March 2, 2009 at 10:11 am
nymgk_75 (3/2/2009)
So always use the db_login for asp.net code....not the actual db_user. Got it.Ahhh, makes sense. Have a 1:1 relationship for a db_user and a db_login. So if I hypothetically had 3 managers that require read-only access, I wouldn't create 1 db_login called "read_login" and associate it to 3 different users? Sounds like simply create 3 different db_users and 3 db_logins!
That's usually how it works, but here's a new wrinkle. If the 3 managers need read access to the same databases, you could include their logins in a Windows group and add the group as a SQL Server login and database user. They'd still connect to SQL Server as themselves, but you'd only have to add one login in SQL Server and one user in each database.
Greg
March 2, 2009 at 10:18 am
Greg Charles (3/2/2009)
nymgk_75 (3/2/2009)
So always use the db_login for asp.net code....not the actual db_user. Got it.Ahhh, makes sense. Have a 1:1 relationship for a db_user and a db_login. So if I hypothetically had 3 managers that require read-only access, I wouldn't create 1 db_login called "read_login" and associate it to 3 different users? Sounds like simply create 3 different db_users and 3 db_logins!
That's usually how it works, but here's a new wrinkle. If the 3 managers need read access to the same databases, you could include their logins in a Windows group and add the group as a SQL Server login and database user. They'd still connect to SQL Server as themselves, but you'd only have to add one login in SQL Server and one user in each database.
This depends. If they are directly connecting to SQL Server, yes. But if they are going through an ASP.NET application, the only way to do this is via Kerberos delegation, which requires a lot more setup. In that case, if they all have the same rights, it might be better to authenticate them on the web server but use a service account (the Network Service, which is the default application pool identity, would map as Domain\ComputerName$ and would work) to connect to SQL Server.
K. Brian Kelley
@kbriankelley
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply