Database Users and Logins

Hi

I can't figure out what the purpose of having seperate users is as I can't actually login to the database using one.

Here is my scenario.

I have a single login called LoginA and I have a database which I want to carve up using schema's. At the database level I need to create a user, associate a login with this user and can set a default schema and specifiy what schemas this USER can access. The login created can access multiple schemas.

So..

I created a database login called loginA.

I created a user for the database called UserA set it's login name to LoginA and

I then created 3 schemas called SchemaA, SchemaB and SchemaC and set their schema owner name to UserA.

I went back to UserA and set their default Schema to SchemaA

How can I login using the new user created as it has no password associated with it. If I login using LoginA then I have no default Schema set becuase the schema is associated with a USER not a LOGIN.

I can understand why you can only have one login account assicated with one user account for each database but I can;t understand why you can specify a user name if you can't use it to login.

Has anyone got any ideas?

Thanks

Logins are used to connect to the server - they provide an identity at the server level. Users provide an identity at the database level. A login will be mapped to a user when he connects to the database.

To have the default schema take effect after login, you should change context to the database where you create UserA (let's say its name is DbA). To automatically do this at login time, you need to additionally change the default database of LoginA to be DbA instead of the master database. A schema is a database entity, so the default schema of a login will be specific to each database to which he will connect.

Thanks
Laurentiu