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'webservices',NULL,@.fUsePattern=1

exec [webservices].[sys].sp_columns_90 N'COLUMN_DOMAIN_USAGE',N'INFORMATION_SCHEMA',N'webservices',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?

Create a user for that login in the master database. Let's say that the login is 'alice', then you have to do something like this:

use master

create user alice

deny select on INFORMATION_SCHEMA.CHECK_CONSTRAINTS to alice

This needs to happen in the master database because CHECK_CONSTRAINTS is a special catalog. You can query it in other databases, but in fact it resides in the resource database and permissions on it have to be granted within the master database. You can see the permissions granted on it by querying the database_permissions catalog in the master database:

select * from sys.database_permissions where major_id = object_id('INFORMATION_SCHEMA.CHECK_CONSTRAINTS')

The deny made in master will work in any other database.

What objects should be accessible depends on what the UPS WorldShip application needs to access. You could try to determine the required subset of objects for which access should be granted to the application by trial and error.

Thanks
Laurentiu

|||Mostly correct.

you would have to add

create user alice for login alice

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407644&SiteID=1&mode=1|||Thanks by the way.|||

You don't need the "for login alice" part in my example because the user that is created has the same name as the login, so SQL will know to map them correctly. Asvin's example uses the clause because the user he creates is named differently than the login to which it is mapped.

Thanks
Laurentiu

|||Hi guys,

I have the same problem with WorldShip 8.0 but I'm using SQL Server 2000 and it just started happening last week.

I was wondering... I thought I had blocked access to all normal tables in the DB the WorldShip is querying, but somehow it is still able to run sp_tables and sp_columns and it takes forever to do so. I am also running it over a VPN.

The "create user xyz in master" syntax doesn't seem to work in SQL Server 2000.

I have created a role for the UPS computer user (ups) and restricted it to SELECT only from the one VIEW in needs to and INSERT into only the one table it needs to however it can still run the sp_tables and sp_columns commands.

Could someone please help me block its access to these two stored procedures?

Thanks!

Robert|||

CREATE USER is new DDL added in SQL Server 2005. The SQL Server 2000 counterpart is sp_adduser.

Thanks
Laurentiu

|||Thanks for that...

I was wondering. I am trying to block access to the sp_tables and sp_columns stored procedures but it doesn't seem to let me. The "public" role has access to run these but I set the permission for the UPS user to deny but it doesn't seem to work. Is there some way for me to do this?

Thanks.|||

How did you deny the permissions? The permissions should be denied in the master database to the UPS user.

use master

go

DENY EXECUTE ON sp_tables to UPS

DENY EXECUTE on sp_columns to UPS

go

|||Thanks,

I tried that and it's still able to run them somehow. ARGH!! Oh well, back to the drawing board.|||

I assume there is a login called UPS. And this login is mapped to a user called ups in each user database. What you are trying to do is to ensure that when the login UPS connects to any database, it cannot execute the procedures sp_tables and sp_columns. I hope I understood your scenario correctly.

If the above is correct, then here's what you should do. Create a user called UPS mapped to the login UPS in the master database. You can do this by

use master

go

CREATE USER UPS FOR LOGIN UPS

go

Now deny the execute permissions on this user UPS in the master database.

use master

go

DENY EXECUTE ON sp_tables to UPS

DENY EXECUTE on sp_columns to UPS

go

It shouldn't matter what permissions are granted to public since DENY to a specific user should override the grant on the public role.

Also, can you run the below queries in the master database and let me know the results. These queries return the permissions on the procedures.

select user_name(grantee_principal_id), * from sys.database_permissions where major_id = object_id('sys.sp_tables')

select user_name(grantee_principal_id), * from sys.database_permissions where major_id = object_id('sys.sp_columns')

|||Asvin,

Thanks a lot for your reply!

I've done exactly what you say. I've verified that the user has EXECUTE denied and it does. However it is still able to run sp_tables.

Unfortunately, since I'm running SQL Server 2000, your test queries don't work. Is there a 2000 version equivalent to the sys.database_permissions?|||

Ah! Didn't realize this was sql server 2000. The query in sql server 2000 should be

select user_name(grantee), object_name(id),* from master.dbo.syspermissions where id=object_id('dbo.sp_tables')
select user_name(grantee), object_name(id),* from master.dbo.syspermissions where id=object_id('dbo.sp_columns')

|||Thanks again Asvin!

Here are the results of those two queries. I found that the best way to show you was with a screenshot, so here is the URL:

http://rob.densi.com/sqlquery.jpg

I'm not quite sure what I'm looking at, but maybe you can tell me if things are set up properly.

Thanks!

Robert|||

You are looking at the permissions that have been granted or denied on the stored procedures sp_tables and sp_columns. To drill down further into what the permissions are can you run these queries

select object_name(id),user_name(uid),* from sys.sysprotects where id=object_id('dbo.sp_tables')

select object_name(id),user_name(uid),* from sys.sysprotects where id=object_id('dbo.sp_columns')

To interpret the results of this query use the documentation at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-p_0837.asp

If you see that the protecttype column is set to 206 for the user UPS and UPS is still able to execute these procedures, some thing is really wrong. Can you also try connecting directly to the master database as UPS and run these stored procedures instead of connecting to a user defined db.