Solved

Cannot connect to SQL Server '05 except as sysadmin role

Posted on 2009-05-07
22
484 Views
Last Modified: 2012-05-06
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.  
0
Comment
Question by:fslattery
  • 10
  • 7
  • 5
22 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 24331404
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
 
LVL 3

Expert Comment

by:adammet04
ID: 24332291
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
 
LVL 1

Author Comment

by:fslattery
ID: 24335254
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 24335460
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
 
LVL 1

Author Comment

by:fslattery
ID: 24335748
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 24336204
It has been too long since I did any of that.

mlmcc
0
 
LVL 1

Author Comment

by:fslattery
ID: 24336356
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 24336431
I asked the zone advisors for a SQL Admin expert to try and help

mlmcc
0
 
LVL 3

Expert Comment

by:adammet04
ID: 24341017
Just to confirm,

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


0
 
LVL 3

Expert Comment

by:adammet04
ID: 24341037
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 24341414
Since the asker is a novice, how does he do that?

mlmcc
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:fslattery
ID: 24355549
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
 
LVL 1

Author Comment

by:fslattery
ID: 24364067
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
 
LVL 3

Expert Comment

by:adammet04
ID: 24371070
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
 
LVL 3

Expert Comment

by:adammet04
ID: 24371088
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
 
LVL 3

Expert Comment

by:adammet04
ID: 24371097
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
 
LVL 1

Author Comment

by:fslattery
ID: 24375655
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
 
LVL 1

Author Comment

by:fslattery
ID: 24388628
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
 
LVL 3

Assisted Solution

by:adammet04
adammet04 earned 50 total points
ID: 24391881
is the server patched up ? how about stoppping and starting the services. maybe even a server reboot (very last resort)?
0
 
LVL 1

Author Comment

by:fslattery
ID: 24395313
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
 
LVL 1

Author Comment

by:fslattery
ID: 24451804
Update: Ran a reboot, didn't fix the problem.  Going to try to restore the master db in case that's the issue.
0
 
LVL 1

Accepted Solution

by:
fslattery earned 0 total points
ID: 24492016
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now