April 13, 2009 at 3:07 pm
I have something weird going on and I can't find a reasonable explanation or solution thus far. We have our ERP database sitting off by itself, we'll call it Live. I have a reporting database called Misc that contains no (significant) table objects, just a bunch of views pointing to Live. I have two User Roles, Admin and Tech. Admin has select on every view in Misc and the base tables in Live, Tech has select permission on most but not all. Then I add the individual users to an OS-level group for the roles, and it's pretty easy to maintain without flooding the database with users.
The Misc database is used for reporting via Access and ODBC.
A few months ago one of the admin reports stopped working with an ODBC Connection Failed. No one with admin permission could run it, but I can run it as my DBA account. It's only run monthly, so I print it directly to the supervisor's printer and all is well. A couple of months ago a second report failed with the same error.
I've broken the system apart checking permissions. I scripted out all objects in Misc to get a list of all base tables then checked permissions of all objects being used and everything appears good, but obviously something is out of whack.
This is all on a SQL Server 2000 Enterprise box with SP4.
I'm almost of a mind to script out all views in Misc and recreate them, but I should be able to identify the problem without going to that extreme.
Any suggestions on what I can look at next?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
April 13, 2009 at 4:00 pm
Very strange, Wayne.
Are you sure the ODBC connections haven't changed? I'm guessing they're NT auth, perhaps some strange connection item?
How are the admin reports run?
April 13, 2009 at 4:10 pm
It's rare that I have easy problems because I try my best to resolve 'em before I post here! 😀
All of the users are NT authority. I add them to the Local Group Report_Admins and Report_Techs, then those two local groups go into the SQL Server roles that are the basis for my permissions. The admin group can run all of the other admin reports except for the two that are failing, and they're all through one ODBC connection.
The overall structure is that the views in the reports database join the data from the live database and apply dates from a parameter table in the reports DB. The users have permission against the base tables in the live system, so I'm not sure what's up.
I think I should do a Profiler trace possibly as my next step, see if that'll show anything interesting.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply