Stuart McLachlan
stuart at lexacorp.com.pg
Fri Feb 25 16:09:54 CST 2011
That all sounds correct.
In Management Studio, right click on the Server (top level Connection) and select Security.
Make sure that "SQL Server and Windows Authentication mode" is ticked.
Note that you can still create SQL Server Authenticated logins even if "Windows
Authentication" is ticked - they just can't log in while that options is toggled.
--
Stuart
On 25 Feb 2011 at 16:22, 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
>
>