Michael-Thomas
asked on
Access to SQL Server Link Tables list showing up as syncobj
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_0x303031424434
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.
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_0x303031424434
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.
ASKER
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?
Do you mean adding read permissions to all objects in the database one by one rather than using the db_datareader role?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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.
You don't have to do that.
ASKER
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?
how can I get around individual grants when the db_datareader role alone doesn't show my list of tables?
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.
exec sp_msforeachtable 'GRANT SELECT ON OBJECT::? TO YourUserName'
Though I suspect there is a lot easier way to do that.
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.
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.
ASKER
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
ASKER
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
Nevermind, I did not realize you were still using SQL Server 2000.
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.