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