[AccessD] Connecting to SQL using Data Properties

At 23/11/2011, David McAfee wrote:
>You can view everything from SSMS logged in as the troubled login?

Sorry my mistake.  I can log into the machine which has SQL server as 
administrator and make changes.  I cannot as the user profile.

> From Access 2007, which is what I have in front of me at the moment, I can
>click on the Office button.
> From there, if I click on Server->Connection, I can choose any server and
>then any database on that
>chosen server (that I have access to). Is this where you are not seeing
>everything logged in as the troubled user?

Yes - this is where the problem is.   If I log in using another 
user's login I can see all the databases in the "Select the Database 
on the Server" dropdown and connect to the database I want.

>David
>
>
>On Tue, Nov 22, 2011 at 1:39 PM, David Emerson <newsgrps at dalyn.co.nz> wrote:
>
> > At 23/11/2011, David McAfee wrote:
> >
> >> Do you have access to SQL Server management studio to see if your login
> >> behaves the same there?
> >>
> >
> > Yes I do have access to Management Studio (Version 2008 R2).  I can view
> > the database, edit stored procedures, change role settings etc.  Is this
> > what you mean?
> >
> >
> >
> >  Could your login be attached to a role that is denied access to the
> >> database in question?
> >>
> >
> > I have removed all the roles except db_ddladmin and db_owner.  Doesn't
> > seem to have helped.
> >
> >
> >  It does sound like permissions to me.
> >>
> >> D
> >>
> >> On Tue, Nov 22, 2011 at 12:35 PM, David Emerson <newsgrps at dalyn.co.nz
> >> >wrote:
> >>
> >> > I have an ADP in Access which I put on a Client's network then link to
> >> > their server using the Data Link Properties:
> >> >
> >> > If I log in using another user's login I can see all the databases in
> >> the
> >> > "Select the Database on the Server" dropdown and connect to the
> >> database I
> >> > want.
> >> >
> >> > On a profile that has been set up for me all I see in the "Select the
> >> > Database on the Server" dropdown is master, model, msdb and tempdb.  I
> >> do
> >> > not see any of the other databases on the server.  I can link to these
> >> and
> >> > the Test Connection is successful.  However the database I want is not
> >> one
> >> > of these.
> >> >
> >> > My initial thought is that I don't have permissions in SQL Server.  I
> >> > checked for the database I want and I have even set all database role
> >> > memberships except denydatareader and denydatawriter in User Mapping.
> >>  In
> >> > the Server Rolls screen I have ticked all rolls.  Status shows I have
> >> > permission to connect granted and login enabled.  Still the database
> >> > doesn't appear in the "Select the Database on the Server" dropdown.
> >> >
> >> > Any suggestions?
> >> >
> >> >
> >> > Regards
> >> >
> >> > David Emerson
> >> > Dalyn Software Ltd
> >> > Wellington, New Zealand