Access Permissions on server scoped objects for login

We are having problems with the response times from UPS WorldShip after switching from SQL Server 2000 to 2005.

I think that the problem can be fixed from the database end by setting the permissions correctly for the user/role/schema that is being used by WorldShip to connect to the server but, I'm not sure how to do it.

The Setup

Client
UPS WorldShip 8.0 running on XP Pro SP2
Connecting via Sql Native Client via SQL Server Login
Connection is over a T1 via VPN

Server -
SQL Server Standard Edition on Windows Server 2003
2x3ghz Xeon processors w/ 4gb ram

The user that is being used to connect runs under it's own schema and role and only needs access to two tables in a specific database on the server.

What UPS WorldShip seems to be doing is on a continual basis retrieving information about the layout of the database via calls such as the following

exec [sys].sp_tables NULL,NULL,NULL,N'''VIEW''',@.fUsePattern=1

exec [webservices].[sys].sp_columns_90 N'CHECK_CONSTRAINTS',N'INFORMATION_SCHEMA',N'dbase name',NULL,@.fUsePattern=1

exec [webservices].[sys].sp_columns_90 N'COLUMN_DOMAIN_USAGE',N'INFORMATION_SCHEMA',N'dbase name',NULL,@.fUsePattern=1

This seems to happen whenever WorldShip contacts the database to find out information in order to be able to create a mapping to the database as well as exporting information to it. Because of the VPN connection these calls take anywhere from 20 seconds to 3 minutes.

I am fairly confident that the problem lies with these calls to the database which I was able to capture using the SQL Server Profiler. We have experimented with the following setups.

1. Connecting to SQL 2000 over VPN with SQL Native Client - No noticeable lag
2. Connecting to SQL 2000 over VPN with SQL Server 2000 driver - No Noticable lag
3. Connecting to SQL 2005 locally with SQL Native Client - No Noticable lag
4. Connectiong to SQL 2005 over VPN with SQL Native Client - Lots of lag

Our network admin has been testing the network connections over the VPN and it is very responsive with none of the long wait times found when using UPS WorldShip.

Now for a possible solution other than getting UPS to fix their software. I think that by limiting the tables and views that the login is able to see will cut down significantly on the lag times that are being experienced. The problem is that there were 264 items that were being returned by sp_tables. I was able to cut that down to 154. I am unable to disable access to any of the rest of the items because they are server scoped.

Take for example the INFORMATION_SCHEMA.CHECK_CONSTRAINTS view. When I try to deny access to it in any way I get the following error:

Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master (Microsoft SQL Server, Error: 4629)

Am I able to deny access to these types of object and if so how? Also, what objects should be accessable such as sys.database_mirroring, sys.database_recovery_status, etc?

Yes, you should be able to deny access to server scoped objects. Create a user in master that is mapped to same login as that of your database user and deny the select permissions to that user. I hope the below example will make it more clear.

Let the database name be dbname, login used to connect to the server be lgn1 and the user corresponding to login lgn1 in dbname be usr1. If i understand your scenario correctly, lgn1 connects to the database dbname and runs the query

exec [sys].sp_tables NULL,NULL,NULL,N'''VIEW''',@.fUsePattern=1

You have denied select permissions on various views to usr1 and managed to reduce the number of objects returned to 154. Now to get it down further , try the following steps.

Connect to the master database and create a user for login lgn1.

use master

go

create user usr_master for login lgn1

go

Next, deny the select permissions on the various object to this user usr_master

DENY SELECT ON INFORMATION_SCHEMA.CHECK_CONSTRAINTS to usr_master.

Repeat for every server scoped object you want to disable access.

Let me know if this works for you

|||Weird. I thought I had tried that. Maybe I didn't link the user to the login. Anyway, it seems to have worked.

You don't by chance have any idea as to what should stay accessible to a client.
|||

Check the TABLE_OWNER column returned by sp_tables. If the TABLE_OWNER is INFORMATION_SCHEMA or SYS, then those views return metadata about the server. If your client absolutely does not require access to any metadata of the server then you can disable access to all objects whose TABLE_OWNER is INFORMATION_SCHEMA or SYS.