[AccessD] Connecting to SQL using Data Properties

David Emerson newsgrps at dalyn.co.nz
Wed Nov 23 15:40:39 CST 2011


Done that to no avail.  Will wait until the administrator can sort 
out my profile issues before wasting more time on this problem.

David

At 24/11/2011, Stuart McLachlan wrote:
>IN SSMS 2005,  while logged in as an administrator:
>
>Go to ServerName - Security - Logins  and select the login in question.
>Right Click and select Properties
>Under "Select a Page", select User Mapping
>Make sure  the databases that you want the Login to be able to 
>access are ticked  in the top
>right hand panel and the correct roles are set in the bottom panel.
>
>--
>Stuart
>
>On 23 Nov 2011 at 12:59, David McAfee wrote:
>
> > From SSMS (2005), if I click on File->Connect Object Explorer
> >
> > I can choose
> >    Server Type : Database Engine
> >    ServerName: YourServerName
> >    Authentication: SQL Server Authentication (Yours is currently set to
> > Windows Authentication)
> >   Login:  limitedUser
> >   password: xxxxx
> >
> > It will show me all databases, but if I click on one that I know he doesn't
> > have rights to, it will give me an error.
> >
> > If I try and exec a stored procedure that he has rights to, it will run.
> >
> >
> >
> >
> > On Wed, Nov 23, 2011 at 12:37 PM, David Emerson 
> <newsgrps at dalyn.co.nz>wrote:
> >
> > > I can only log in as administrator to the server.  SSMS requires Windows
> > > Authentication to log in.  I can't see how I can log into the server as
> > > Administrator, then log into SSMS as mu own log in.
> > >
> > >
> > > At 24/11/2011, David McAfee wrote:
> > >
> > >> 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>
> > >> 
> <http:**//databaseadvisors.com/**mailman/listinfo/accessd<http://databaseadvisors.com/mailman/listinfo/accessd>
> > >> >
> > >> > Website: http://www.databaseadvisors.****com<http://www.**
> > >> databaseadvisors.com <http://www.databaseadvisors.com>>
> > >>
> > >> >
> > >> --
> > >> 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>
> > >>
> > >
> > > --
> > > 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>
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com




More information about the AccessD mailing list