David Emerson
newsgrps at dalyn.co.nz
Wed Nov 23 15:38:08 CST 2011
I tried that and couldn't even log in as any user. There seems to be other problems with the profile the Administrator set up for me so I will wait until he gets them sorted out before trying again. Thanks for your suggestions. David At 24/11/2011, 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