David Emerson
newsgrps at dalyn.co.nz
Wed Nov 23 16:34:36 CST 2011
Finally solved the problem. I got the administrator to delete my login totally from SQL and recreate it. Now I am in. Thanks David (and Stuart) for your help. At 24/11/2011, David Emerson wrote: >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