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
 CREATE TABLE #temp
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
TABLE_TYPE varchar(32),
REMARKS varchar(254)
)

INSERT INTO #temp
exec sp_tables NULL,NULL,NULL,N'''VIEW'''

select
@permission + '[' + TABLE_QUALIFIER +'].[' + t.TABLE_OWNER + '].['+ t.TABLE_NAME + '] TO [' + @userid + ']'
--@permission + '[' + t.TABLE_OWNER + '].['+ t.TABLE_NAME + '] TO [' + @userid + ']'
from #temp t
where t.TABLE_OWNER in ('sys','INFORMATION_SCHEMA')

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