WorldShip SQL2005 sp_Tables

If you're reading this post then you are probably looking for a way to limit the number of table/view records returned by sp_tables. 

For some reason each time Worldship opens an import/export mapping it runs

exec sp_tables NULL,NULL,NULL,N'''TABLE'''
exec sp_tables NULL,NULL,NULL,N'''VIEW'''

to return a list of tables.  This includes sys and INFORMATION_SCHEMA tables that won't be used.  Then the columns from each of the tables is returned.  Overall this can add lots of processing time overhead to a small select/insert statement.

To reduce the rows returned by sp_tables I had to DENY SELECT to the views.  Here's how I did it

1.  Create a SQL user for Worldship access.

2.  Run the following SQL code to generate the DENY SELECT statements:

Declare @userid varchar(50)
set @userID = 'myUser'
DEclare @permission varchar(50)

set @permission = 'REVOKE SELECT ON '
 set @permission = 'DENY SELECT ON '

--Create temp table
TABLE_OWNER sysname,
TABLE_NAME sysname,
TABLE_TYPE varchar(32),
REMARKS varchar(254)

exec sp_tables NULL,NULL,NULL,N'''VIEW'''

@permission + '[' + TABLE_QUALIFIER +'].[' + t.TABLE_OWNER + '].['+ t.TABLE_NAME + '] TO [' + @userid + ']'
--@permission + '[' + t.TABLE_OWNER + '].['+ t.TABLE_NAME + '] TO [' + @userid + ']'
from #temp t

drop table #temp

3.  Execute the DENY SELECT statements in the master and your working database.

4.  Revoke the deny for these tables so that ODBC will work.

revoke  SELECT ON [sys].[syscharsets] TO myUser
revoke  SELECT ON [sys].[sysdatabases] TO myUser

The permissions can be tested by running sp_tables with the user account

 exec sp_tables NULL,NULL,NULL,N'''TABLE'''
exec sp_tables NULL,NULL,NULL,N'''VIEW'''

before and after executing the generated statements.

If you also wanted to remove dbo tables from sp_tables you could also create DENY SELECT statements for those tables.


Print | posted on Tuesday, June 16, 2009 2:52 PM


# re: WorldShip SQL2005 sp_Tables

left by Darin at 12/8/2009 6:38 PM Gravatar
THANK YOU! We tried to solve this a couple years ago, but couldn't (UPS was no help, even after getting through to a technician), so we reverted to SQL Server 7 that didn't have the issue. We ran into the problem again yesterday after we had to migrate from SQL Server 7. I we were tearing our hair out and had all but given up, until I found your post. You've made a lot of people happy.

# re: WorldShip SQL2005 sp_Tables

left by Joe F at 12/29/2009 9:52 AM Gravatar
Hello, this all makes sense. Thank you. But it dosn't work for me. The syntax only executes in the master database then only when the target user account present in the master db. It says the command completed successfully but on inspection the deny is not there! Perhaps a service pack has removed this capability? I can't even do it via SQL Managemtn Console, it comlains I must be do it the context of the master db!

# re: WorldShip SQL2005 sp_Tables

left by Joe F at 12/29/2009 10:06 AM Gravatar

Pleas disregard the last comment. It suddenly worked, no idea why. Thanks again.
Post A Comment