We help IT Professionals succeed at work.

Access to SQL Server Link Tables list showing up as syncobj

Medium Priority
982 Views
Last Modified: 2012-05-12
I am trying to setup a linked tables to SQL Server 2000 database where the user only has read permission.

I created an SQL user and added them to the db_datareader role, then I created an ODBC connection in my Data Sources.  When I go to add the tables in Access through External Data -> ODBC Database, ->"Link to the data source by creating a linked table" the list of tables shows up as dbo.syncobj_0x3030314244344641 (as an example).

If I change the user's permissions from the db_datareader role to the db_owner role, all the table names display correctly.  I ran SQL profiler to see what was being run and I found that master.dbo.sp_tables was being executed so I granted execute permissions to that stored procedure for that user, but that did not solve my problem.



 Linked Tables view of syncobj
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
The SQL user you added is in the ODBC data source config right?
I believe that sp_datareader does not give the user rights to ALL SQL system objects and that's what is missing - is not enough to grant access to sp_tables only. I suggest you add a custom system_user role and grant read/exec rights to all system_user role then add your user(s) to that role besides the db_datareader. And I think this should be done in the user db, master db and msdb if there are any jobs/mails invoved.

Author

Commented:
Yes, the user is the same for the ODBC configuration.  

Do you mean adding read permissions to all objects in the database one by one rather than using the db_datareader role?

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
Leave db_datareader for all NON system objects read access but you must grant read and exec rights to all sys objects similar to what you did for "sp_tables". Unless you trace the process step by step and grant only very speciffic and limitted rights this is the better alternative than db_owner
I tried adding select/exec privileges to the system objects, but that didn't work.

I decided to try an explicit select permission on one of my tables and that resolved my issue.  There must be some sort of internal difference between select permission and the db_datareader role.

It would have been nice not to have to grant select on each of the 300+ tables, but such is life with SQL Server.
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>It would have been nice not to have to grant select on each of the 300+ tables, but such is life with SQL Server. <<
You don't have to do that.

Author

Commented:
I did delete all the syncobj_xxxxxxxx views to avoid the clutter, so now it only shows a blank list unless I grant the select privilege.

how can I get around individual grants when the db_datareader role alone doesn't show my list of tables?
CERTIFIED EXPERT
Top Expert 2012

Commented:
You should be able to ahndle this with db_datareader, but if you insist you can do something like this:
exec sp_msforeachtable 'GRANT SELECT ON OBJECT::? TO YourUserName'

Though I suspect there is a lot easier way to do that.
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
as mentioned above - I would add a new role called "system_users" or "sys_obj_access" whatever you prefer (the name does not matter) in that database and grant all SQL sys objects rights then add your user to the role same as you did to db_datareader.

If I'm not mistaken (as SQL 2000 is pretty old) in order to grant access to all sys objects in SQL 2000 you can get a list of all system tables from sysobjects where xtype = 'S' (S = System table) and name like 'sp_%' for all system stored procedures. Unfortunately I don't have one installed anymore but this should give you the picture - just make sure you grant minimum rights on sys objects like exec and select only.

db_datareader will take care of all the user tables so you dont have "....to grant select on each of the 300+ tables"

And BTW...I found it myself that life is much easier with SQL comparing to many other databases and I can tell that as I work(ed) with many different db's so far.

Author

Commented:
using the standard db_datareader role would not allow the tables to appear in the pick list for the ODBC connection in MS Access.  The only method I have found to work so far is explicitly using a GRANT SELECT on each table

Author

Commented:
using the standard db_datareader role would not allow the tables to appear in the pick list for the ODBC connection in MS Access.  The only method I have found to work so far is explicitly using a GRANT SELECT on each table
CERTIFIED EXPERT
Top Expert 2012

Commented:
Nevermind, I did not realize you were still using SQL Server 2000.