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

Article translations Article translations
Article ID: 266766 - View products that this article applies to.
This article was previously published under Q266766
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 58095 (SQLBUG_70)
Expand all | Collapse all

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:

Collapse this imageExpand this image
DOWNLOAD
Download S70843i.exe (Intel) now
Collapse this imageExpand this image
DOWNLOAD
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.mspx

Properties

Article ID: 266766 - Last Review: November 2, 2013 - Revision: 5.0
Applies to
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbdownload kbbug kbfix kbgraphxlinkcritical kbqfe KB266766

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com