[AccessD] Connecting to SQL using Data Properties

David McAfee davidmcafee at gmail.com
Wed Nov 23 11:28:19 CST 2011


What happens if you log into SSMS as the problem user? What do you see?

Still sounds like privileges to me.



On Tue, Nov 22, 2011 at 4:30 PM, David Emerson <newsgrps at dalyn.co.nz> wrote:

> 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
>>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/**mailman/listinfo/accessd<http://databaseadvisors.com/mailman/listinfo/accessd>
> Website: http://www.databaseadvisors.**com<http://www.databaseadvisors.com>
>



More information about the AccessD mailing list