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