Cannot connect to SQL Server '05 except as sysadmin role

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.  
LVL 1
fslatteryAsked:
Who is Participating?
 
fslatteryAuthor Commented:
Finally was able to schedule some downtime to restore the master db last night.  Backed up all our production DBs on the server, wiped it, restored the master db, then restored the production dbs, and for whatever reason it worked.  This leads me to believe that the issue was due to some sort of problem with the master db or one of the other system dbs, but I can't say for certain.
0
 
mlmccCommented:
Have you set the permissions for FOO to read for all the tables?

Have you created a new READONLY group that just has read permissions and put FOO in it?

mlmcc
0
 
adammet04Commented:
A quick question, has the error always occured or maybe only since the database was moved to a new server?

if so the foo user could be orphaned in the database.

USE yourDatabasenameHere
EXEC sp_change_users_login @Action = 'Auto_Fix', @UserNamePattern = 'foo'


when the user is granted access to a database it should also be given access to connect to the server.
strange issue


0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
fslatteryAuthor Commented:
mlmcc: I set foo to db_datareader for the target db.  Do I need to set explicit grants for each table, or shouldn't access be allowed by default under the public role?  I put an explicit deny on the tables I need hidden for the login.

adam: We've been running the server for a while.  It's mainly used as a backend for one of our web apps, and any connection directly to the database has been done by administrators/management who don't need to be blocked from anything, so we've just been using a sysadmin login.  I tried the command you suggested but no luck.  Any take on why it'd work with sysadmin role assigned?  Is there some setting that I'm missing?
0
 
mlmccCommented:
You may.  We restricted our users to the views and stored procedures thus they couldn't get at the data directly on;y the views we decided to provide.

mlmcc
0
 
fslatteryAuthor Commented:
That's what I'm seeing a lot in terms of generally accepted best practices, and I'd like to move towards that eventually.  I appreciate the suggestion.

I'm generally more experienced with writing queries than trying to administer sql server, so perhaps I'm not following the right procedure for adding a login with access to a database?
0
 
mlmccCommented:
It has been too long since I did any of that.

mlmcc
0
 
fslatteryAuthor Commented:
Then I envy you greatly!  I figure I'll leave this open to see if anyone has any ideas.  I'll close it I can find a solution through one of my personal contacts.
0
 
mlmccCommented:
I asked the zone advisors for a SQL Admin expert to try and help

mlmcc
0
 
adammet04Commented:
Just to confirm,

have you tried removing and re adding the sql server login?


0
 
adammet04Commented:
Something else to look at.. is the public role or any other groups in the database explicitly denied read access? maybe foo is a part of one of the roles / groups that has been denied read access?
0
 
mlmccCommented:
Since the asker is a novice, how does he do that?

mlmcc
0
 
fslatteryAuthor Commented:
I'll look into that Adam, thanks.  Regarding removing/readding, I did remove and re-add at one point because it wasn't working.  I don't remember denying any access to anything other than one particular table in the database I'm pulling from.  
0
 
fslatteryAuthor Commented:
I've checked and I can't find any denials of access anywhere.  I'm using the express edition of the management studio, would that have an effect?  Also, I see references online related to only one user being able to access the db at a time if the DB is set a certain way, would that be the issue, and how would I check that?
0
 
adammet04Commented:
You can look at that by right clicking the db and selecting the properties

Select Options

Scroll to the bottom of the other options section

In Restrict Access the value should be MULTI_USER

0
 
adammet04Commented:
To check a users security

right click the user under security, select General, have a look to see what roles the user is a part of.

common values are to have db_datareader, db_datawriter selected or db_owner selected.

You can also check the securables section as well.


0
 
adammet04Commented:
Sorry

another stupid question,

if you create another user called 'foo2' and grant it read access to the system , can you log in with that user ok?
0
 
fslatteryAuthor Commented:
DB is set to MULTI_USER.  Tried setting to db_owner as well as two custom roles I created with the permissions I wanted, still no luck.  Creating new, fresh users hasn't worked for me either.  None of your questions are stupid though.  I think that the solution might be something extremely basic, I'm hoping it's ultimately just some overlooked minor thing so it'll be a quick fix.
0
 
fslatteryAuthor Commented:
Just as a comment, I had an acquaintance of a coworker come in to take a look at the issue.  Skilled professional, 15+ years experience with SQL db administration, really bright guy.  Worked with him for over half an hour and he was unable to figure out the problem, since everything theoretically is configured correctly.  From the error codes and his fiddling with the server and db settings, we're pretty positive it's an issue with logging into the server and not accessing a particular database within it.  The error code signifies "Valid login but server access failure".
0
 
adammet04Commented:
is the server patched up ? how about stoppping and starting the services. maybe even a server reboot (very last resort)?
0
 
fslatteryAuthor Commented:
Services have been started and stopped, not sure we've had a reboot in a while, so we'll check up on that when we can spare a few min of downtime.  I know we're up to date on OS updates.  Will look into applying any patches we don't have and rebooting, and will post an update.
0
 
fslatteryAuthor Commented:
Update: Ran a reboot, didn't fix the problem.  Going to try to restore the master db in case that's the issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.