FIX: Temporary Stored Procedures in SA Owned Databases May Bypass Permission Checks When You Run Stored Procedures (266766)

SYMPTOMS

Under the following conditions, stored procedure execution permission checks do not work properly and they allow access when access should not be allowed:

A temporary stored procedure is created by a non-dbo user that references a stored procedure owned by dbo.
The database where the referenced stored procedure exists is owned by the standard system administrator (sa) security login.
The non-dbo user does not have EXECUTE permissions on the referenced stored procedure.

WORKAROUND

To work around this problem, change the owner of the database to another valid login other than sa.

NOTE: The owner of the system databases (master, model, and tempdb) cannot be changed.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider. If you are running SQL Server Service Pack 2 and you cannot upgrade to Service Pack 3, visit the following Microsoft Web site to download the fix:

Download S70843i.exe (Intel) now
Download S70843a.exe (Alpha) now

Release Date: Jul-7-2000

MORE INFORMATION

This problem typically occurs on ODBC-based client applications that use ODBC drivers earlier than version 3.70.623 and have the Generate Stored Procedures for Prepared Statement option enabled for the data source. However, if the Odbccmpt.exe utility is used to set the client application to use the old ODBC behavior, the problem can also occur.

NOTE: This does not allow the non-dbo user to modify the referenced stored procedure in any way.

For additional information, please see the following Microsoft Security Bulletin:

http://www.microsoft.com/technet/security/bulletin/ms00-048.asp