What's the difference: Login, User, Login ID, etc???

[TroyMcClure]

OK, I'm undergoing mass confusion as to what's a User and a Login in sql 2000.

In EM, I try to create a user, but it asks for a login??!! I thought a user was a login? Anyway, it lists 'sa' as the only one in the dropdown at the top of the 'new user' dialog, so here are the 3 logical possibilities and the result:
1. I choose 'sa'--the ONLY thing in drowpdown, it and it says I can't use the reserved name sa!!

2. So I just type in anything, figuring this is a 'new' user, but it gives me the error that it doesn't exist!

3. Ok, I find one that *does* exist by looking in sp_helpuser list, and it says that already exists.

What is up with that!? Is this not a catch 22?


And how do the two authentication 'modes' differ:
NT authentication
...and
sql authentication
...when doing an odbc connection? Is Sql server itself in one of these 'modes'? Or are these authentication modes set database by database? Can these modes overlap? Can I create an odbc db connection using either or both methods?

I guess I just need a clear explanation of the different modes of authentication, and how and when each one is in effect, and if they can overlap, and what's up with users and logins. Thanks for any light on this,
Troy

 

[mrdenny]

A Login lets you into the Server it self.

A user is an alias giving logins access to the database.

Example
I grant access to the domain account WORK\mrdenny a login into the database at WORK\mrdenny.

I then create a user in the database Intranet called mrdenny, which is mapped to the login WORK\mrdenny.

Before you can create a user in a database, there must be a login to go with it. To create a login in EM, open the server, open security, select Logins. This is where you create logins. Both Trusted and SQL logins.

Once the login is created on the server it will appear in the dropdown to user the user access to the database.

To help keep is straight; 'You "log in" to the server, and "user"(r) a database.'

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[TroyMcClure]

mrdenny,
Thank you, I'm getting there. But in the dropdown of the user dialog in one of the databases on this server, I only see:
<new>
sa

...and that's it, but in the security Login page, I see:
BULITIN\Administrator
MYDOMAIN\Administrator
MYDOMAIN\someuser1
MYDOMAIN\someuser2
sa

...so why don't I see all of those in the User dialog? And is a 'Trusted' connection just another term for a Windows Domain User as opposed to a SQL Server user? Which begs another question: If 'Trusted' connection is a login that is *any* domain user, does that mean that even the lowly 'Users' group users on the Domain get server access?

Thanks for helping me sort this out...
Troy

 

[mrdenny]

Do any of the accounts that arn't showing up listed in the users section of the database? Look in the &quot;Login Name&quot; column.

A trusted connection is a domain user.

Any domain user can be granted access via there account, or by granting a domain group that they are in access to the server. So the Users group on the domain will only get access if you grant access to the MYDOMAIN\Users. No access is granted automatically except for BUILTIN\Administrators which is the Administrators group on the SQL Server.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLBill]

The Logins may not have been given access to a database. In EM expand down to Security. Expand that and click on Logins. Find the login that you want to check, right click on the login and select Properties. Go to the Database Access tab. Are any databases checked? If not, then the login may not have access to any database. Now go to Server Roles tab, anything checked there? If nothing is checked on either tab, then the LOGIN does not have access to any database and is therefore not a USER of a database. You can give a Login access to a database by checking the database on the Database Access tab and then in the bottom window checking the proper access. Once that is done, the LOGIN is now a USER of a database.

-SQLBill

 

[TroyMcClure]

SQLBill,
Thanks very much to you and Mrdenny.
So are there 2 ways to add the User to the database? It sounds like I can add a Login as a User by checking the Database box in the Security section (of the entire server), or go to the individual database, select Users, then do 'New'?
Or...do I have to check the Database box in the Security/Login's dialog for that Login *first*, and then that login is visible in the dropdown in the Users dialog, at which point I complete the procedure and add him?
Troy

 

[SQLBill]

You can do it either way IF the LOGIN already exists in Security.

If you add the LOGIN as a USER to a database in the Security area, you don't need to do anything else in the Database Users area.

-SQLBill