October 23, 2008 at 12:46 am
I'm trying to test a DSN with a login that has db_datareader permission on a few databases. I'm getting the following error..
Microsoft SQL Server ODBC Driver Version 03.85.1117
Running connectivity tests...
Attempting connection
Connection established
Verifying option settings
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied on object 'syscharsets', database 'mssqlsystemresource', schema 'sys'.
Disconnecting from server
TESTS FAILED!
A few days ago a script was run that will revoke all select permission on public role. This is the first time I've come across a database called mssqlsystemresource!! Would anyone have some thoughts on this error and ways to correct it?
October 23, 2008 at 8:19 am
Why do you want to access it via ODBC?
This database is by default not visible through SSMS. Here's an article about how to access it: http://weblogs.sqlteam.com/mladenp/archive/2007/03/12/60132.aspx
Chris.
Chris.
October 23, 2008 at 9:40 am
I'm running into the same issue but I'm trying to connect to a user database.
October 23, 2008 at 9:45 am
Geek101, You better post that question on a new thread, you're far more likely to get help that way.
October 23, 2008 at 10:52 am
Well I think either people have misunderstood the post or I didn't give enough information to it. Anyways here's more info guys..
I too 'm trying to access a user database thro this dsn. This DSN is configured to connect to one of the user database and thats when it throws up the error. I think it has to do with permission problem though I'm unaware of how to resolve this...
I think we can figure out the problem if we can find the answers for this.. Does anyone know of the process that happens when a login is authenticated and allowed connections in a database? Which are the tables it checks to be sure that it is a valid login.
My guess is these..
sys.databases
sys.configurations
sys.charset
October 23, 2008 at 11:24 am
I'm quite sure the JK and myself are experiencing the exact same problem. Strange part about it is that after doing a...
GRANT SELECT ON sys.configurations TO PUBLIC
GO
GRANT SELECT ON sys.syscharsets TO PUBLIC
GO
then the account was able to connect through SSMS.
When I created a totally new Windows account then I was able to successfully add a DSN. The problem that I'm having is limited to a user & login that I deleted and recreated.
Can anybody tell me if it's normal for the mssqlsystemresource files to be dated 10/14/2005?
October 24, 2008 at 2:37 am
Can anybody tell me if it's normal for the mssqlsystemresource files to be dated 10/14/2005?
If I remember correctly the date of the files depends on your Servicepack/Hotfix level. A date of 10/14/2005 suggests to me that no SP is installed.
Anyhow, the problem you are facing is almost certainly caused by removing the select permissions for Public on some systemviews. The systemviews use (hiden) tables in the (hidden) mssqlsystemresource database. But when a connection is made the user needs to query certain information from system tables\views like which databases exist on the server, does the login has permissions to access certain databases etc.
Because of that it's never a good idea to remove the default select permissions for public on any system objects.
For the same reason the guest user in master is not disabled, so that processes whichout explicit permissions in master can query the necessary system information.
[font="Verdana"]Markus Bohse[/font]
December 6, 2010 at 4:24 pm
I ran into same problem. Because, we block so many permissions through some process. But got resolved after some troubleshooting. Hope any of you still looking for results:
Who so ever database are you trying to access, you are missing the SELECT permission to PUBLIC.
USE MASTER
GO
GRANT SELECT TO PUBLIC
GO
This will solve your problem right away, and again if you end up with another error:916, then you can give this user the permissions to msdb database..or can run the same (above) commands for msdb database as well.
May 3, 2011 at 11:21 am
I know this is an old thread, but I also have another possible solution in case anyone else encounters this problem.
We were also getting an odbc connection error on mssqlsystemresources when attempting to connect to a user database,
In order to resolve it, we had to put the fully qualified domain name into the ODBC's server path.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply