jwcolby
jwcolby at colbyconsulting.com
Fri Feb 25 20:28:36 CST 2011
Well I finally got to connect and link a table. I did so by editing a DSN file and adding the
username / password there.
John W. Colby
www.ColbyConsulting.com
On 2/25/2011 4:22 PM, jwcolby wrote:
> 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.
>>
>>
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>