You are currently offline, waiting for your internet to reconnect

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

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)
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.
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.
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:

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:
st proc sproc sp sp1 sp2 sp3

Article ID: 266766 - Last Review: 11/02/2013 23:42:00 - Revision: 5.0

Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbdownload kbbug kbfix kbgraphxlinkcritical kbqfe KB266766