[dba-SQLServer] [AccessD] I'm getting nowhere

OK, Stuart (or anyone capable) - can you walk me through this?

I go into SQL Server Management Studio (Express).
I click the Security folder / logins.
I create a new login called DiscoApp.
I select SQL Server security.
I enter a password (twice).
I select a default database.

Now comes my first question, can this login work with many different databases?  I assume I just set 
that in mapping?

In server roles I leave it "Public"
In User Mapping I select the two (so far) databases that this "user" is mapped to.
In Database role membership for each database I select Public, db_DataReader and db_DataWriter.
In Securables I do nothing
In Status I leave "permission to connect = Grant" and "Login = enabled"


Now I try to connect in my Access database and:

I select New datasource.
I select SQL Server as the driver
I type in DiscoApp as the connection name
I type in a description and select the server instance
I select With SQL Server authentication and type in the username and password from above

and... I get:

Connection failed:
SQL State 28000
SQL Server error 18452
{bunch of stuff here] Login failed for user DiscoApp.  The user is not associated with a trusted SQL 
Server connection.

And here we sit.

If you have any idea what I am doing wrong, please let me know.  I can go no further.

Thanks,


John W. Colby
www.ColbyConsulting.com

On 2/19/2011 7:22 PM, Stuart McLachlan wrote:
> On 19 Feb 2011 at 14:53, jwcolby wrote:
>
>> If I could find something that started at the
>> "This is SQL Server security" basics I could learn this stuff.
>
> Maybe this will help.
> (This is "my" understanding of it - corrections from others welcome .)
>
> There are two levels of "Security" in SQL Server:
>
> 1. SQL Server Instance (Server name) level
> 2. Database level
>
> At the Instance Level, you have:
> 1. Server Roles
> 2. Logins
>
>
> At the Database level you have:
> 1. Database Roles
> 2. Users
>
>
> INSTANCE LEVEL
> ==============
>
> SERVER ROLES
> These are  generic sets of "rights" which apply to the entire Instance.  "Server role is used to
> grant server-wide privileges to a user" . Generally, use Public for all logins unless you need
> admin rights on the server.
>
>
> LOGIN
> To allow anyone to access SQL Server, you need to create a login at instance level for them
> and then define what that login can do in terms of individual databases
>
> Login =  an entity that can log in to SQL Server. In your situation, you are using SQL Secruity
> so  a Login needs a Username and Password.   If using Windows/Mixed security, it could
> also be an Active Drectory user.  Note the use of the word "entity" -  not person.
>
> With SQL Security, an entity  is entirely identified by the username/password pair.
>
> If you embed a standard username/password in a connection string for an application that
> connects to SQL Server, then that application itself is the logged in entity.
>
> Alternatively, if you  collect the username/password from the person using that application
> and put that the in the connection string, the individual user is the entity.
>
> On creation, you can define the "Default Database" for the login - this is the one they
> automatically access  (so your don't need to specify it in your connection string.)
>
>
> DATABASE LEVEL
> ===============
>
> DATABASE ROLE
> Role = a definition a what entities with that role can do in the database.. There are a number
> of predefined roles, which are useful for things like "read only" users but you frequently need
> to  create your own and assign rights to specific database objects for that role.  i.e. allow read
> only on some tables and write access on others.   You can also do things like prevent users
> from directly writing to any tables and only allow them to run specfic stored prcedures to
> update data.
>
> Once you have defined  a new role within the database, you can assign that role to specific
> users within that database.    You can think of a role as similar to a Group, it defines a set of
> rights and you can assign roles to users in the same way you assign "group membership" to
> Windows users.
>
> USER
> Once you have created a Login, you go the relevant database ( or databases) and assign
> rights to that login in that database.  You do that by adding the Login as a User in that
> database.
>
> User = The definition of what a particular login entity can do in the database.
> To make a specific login a user  in the database, you create a new user and select the
> existing Login name.  Note that you can give that user the same name as the Login name or
> use a completely different one.     Unless you have a good reason, I'd use the same as the
> login name.  You then assign  Databse Roles to the User to control what the user can do in
> the database.
>
>
>