[AccessD] Connecting to SQL using Data Properties

Stuart McLachlan stuart at lexacorp.com.pg
Wed Nov 23 15:29:07 CST 2011


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
> 






More information about the AccessD mailing list