Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Cannot connect to SQL Server '05 except as sysadmin role

Avatar of fslattery
fslattery asked on
Crystal ReportsMicrosoft SQL ServerMicrosoft SQL Server 2005
22 Comments1 Solution544 ViewsLast Modified:
I'm connecting to an instance of SQL Server 2005 running on Windows Server 2003.  I need to create a general-purpose login that can access all the tables in a target db except for one (everything read-only).  I have the server set on mixed mode authentication, and I'm using SQL Server authentication for the login.  

The error code I generally get is due to the login existing but access to the database not being allowed (code 12).  When I access from Crystal Reports 2008, I only get that error message.  When I connect to the server itself using third-party administration/dev apps, I can sometimes connect correctly, but can't read table data.  All these problems are solved when I set my general access login (let's call it "foo") to the sysadmin role.  This allows foo to connect as if it were the Sa account, and everything runs smoothly.  The problem is that I can't use this solution as I have to have a very limited, read-only account to release to a broad range of users.

Multiple connections aren't the issue since I've been connected under Sa and foo on my workstation while a colleague was connected to Sa on his.

I've spent quite some time adjusting settings in Management Studio Express and a third-party administrative program as well as exhausting all ideas on Google using the error codes I got, but I'm not an inch closer to solving this.  
ASKER CERTIFIED SOLUTION
Avatar of fslattery
fslattery

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 22 Comments.
See Answers