FIX: When you run a program or a Web browser script that uses the Command object in the ADO programming interface to run a SQL Server stored procedure, you may receive an "EXECUTE permission denied on object..." error message in SQL Server 2000 (819829)

SYMPTOMS

When you run a program or a Web browser script that uses the Command object in the Microsoft ActiveX Data Objects (ADO) programming interface to run a SQL Server stored procedure, you may receive an error message that is similar to the following:

EXECUTE permission denied on object '<Nested Procedure Name>', database '<Database Name>', owner '<Owner Name>'.

This problem occurs when all the following conditions are true:

The EXECUTE Transact-SQL statement that is used to call the stored procedure is prepared by using the Prepare property of the Command object.
The main stored procedure that is run from the program or from the Web browser script makes a call to a nested stored procedure.
The user account that is used to connect to the SQL Server database has EXECUTE permissions on the main stored procedure, but does not have EXECUTE permissions on the nested stored procedure.

RESOLUTION

Service pack information

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 How to obtain the latest SQL Server 2000 service pack

Hotfix information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

   Date         Time   Version            Size    File name
   -----------------------------------------------------------------------
   31-May-2003  06:15  2000.80.818.0      78,400  Console.exe      
   24-Jun-2003  12:31  2000.80.818.0      33,340  Dbmslpcn.dll     
   24-Apr-2003  13:42                    786,432  Distmdl.ldf
   24-Apr-2003  13:42                  2,359,296  Distmdl.mdf
   29-Jan-2003  13:25                        180  Drop_repl_hotfix.sql
   23-Jun-2003  10:10  2000.80.837.0   1,557,052  Dtsui.dll        
   23-Jun-2003  10:10  2000.80.837.0     639,552  Dtswiz.dll       
   23-Apr-2003  14:21                    747,927  Instdist.sql
   02-May-2003  13:26                      1,581  Inst_repl_hotfix.sql
   07-Feb-2003  18:10  2000.80.765.0      90,692  Msgprox.dll      
   31-Mar-2003  13:37                      1,873  Odsole.sql
   04-Apr-2003  13:16  2000.80.800.0      62,024  Odsole70.dll     
   07-May-2003  08:11  2000.80.819.0      25,144  Opends60.dll     
   02-Apr-2003  09:18  2000.80.796.0      57,904  Osql.exe         
   02-Apr-2003  10:45  2000.80.797.0     279,104  Pfutil80.dll     
   22-May-2003  10:27                     19,195  Qfe469571.sql
   12-Jun-2003  04:07                  1,083,989  Replmerg.sql
   04-Apr-2003  09:23  2000.80.798.0     221,768  Replprov.dll     
   07-Feb-2003  18:10  2000.80.765.0     307,784  Replrec.dll      
   04-May-2003  11:35                  1,085,874  Replsys.sql
   31-May-2003  12:31  2000.80.818.0     492,096  Semobj.dll       
   31-May-2003  05:57  2000.80.818.0     172,032  Semobj.rll
   28-May-2003  11:59                    115,944  Sp3_serv_uni.sql
   31-May-2003  12:31  2000.80.818.0   4,215,360  Sqldmo.dll       
   07-Apr-2003  05:14                     25,172  Sqldumper.exe    
   19-Mar-2003  05:50  2000.80.789.0      28,672  Sqlevn70.rll
   01-Jul-2003  11:48  2000.80.834.0     180,736  Sqlmap70.dll     
   07-Feb-2003  18:10  2000.80.765.0      57,920  Sqlrepss.dll     
   23-Jun-2003  10:10  2000.80.837.0   7,553,105  Sqlservr.exe     
   07-Feb-2003  18:10  2000.80.765.0      45,644  Sqlvdi.dll       
   24-Jun-2003  12:31  2000.80.818.0      33,340  Ssmslpcn.dll     
   31-May-2003  12:31  2000.80.818.0      82,492  Ssnetlib.dll     
   31-May-2003  12:31  2000.80.818.0      25,148  Ssnmpn70.dll     
   31-May-2003  12:31  2000.80.818.0     158,240  Svrnetcn.dll     
   31-May-2003  06:29  2000.80.818.0      76,416  Svrnetcn.exe     
   30-Apr-2003  11:22  2000.80.816.0      45,132  Ums.dll          
   01-Jul-2003  11:49  2000.80.834.0      98,816  Xpweb70.dll

Note Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.

WORKAROUND

To work around the problem, use one of the following methods.

Method 1

In your program or in your Web browser script, do not prepare the statement to call the stored procedure. To do this, set the Prepare property of the Command object to FALSE.

Method 2

In the SQL Server database, grant EXECUTE permissions on the nested stored procedure to the user account that is used by the program or by the Web browser script to connect to the database.

Note Contact your SQL Server database administrator before you grant EXECUTE permissions to the user account.

Method 3

Insert a dummy SELECT statement in the main stored procedure just before the EXECUTE statement that calls the nested stored procedure, and then re-compile the main stored procedure. The dummy SELECT statement can be similar to the following:

select 1

MORE INFORMATION

If the user account has EXECUTE permissions on the main stored procedure, the main stored procedure must call the nested procedure successfully, even if the user account does not have EXECUTE permissions on the nested stored procedure.

Steps to Reproduce the Behavior

Start SQL Query Analyzer.
Log in to your SQL Server database as the System Administrator.

Run the following Transact-SQL script by using SQL Query Analyzer.

Note If testlogin is a valid user name, and mainproc and nestedproc are valid stored procedure names for your SQL Server database, you must replace them with a different user name and different stored procedure names.

use pubs
go

-- Create the nestedproc stored procedure
create procedure nestedproc
as
begin
	declare @i int
	select 'OK'
end
go

-- Create the mainproc stored procedure
create procedure mainproc 
as
begin
	declare @i int
	set @i=1

	--  Nested stored procedure call
	exec nestedproc
end
go

-- Create login and grant access
exec sp_addlogin 'testlogin'
exec sp_grantdbaccess 'testlogin','testlogin'
exec sp_defaultdb 'testlogin','pubs'

grant execute on mainproc to testlogin
go

Create a Microsoft Visual Basic script file that is named Test.vbs by using the following code.

Note Replace SQL Server Name with the name of your instance of SQL Server.

Dim cn
Dim cmd
Dim rs
Dim params
Dim param
set cn=createobject("ADODB.Connection")
set cmd=createobject("ADODB.Command")
set rs=createobject("ADODB.Recordset")

cn.Provider = "SQLOLEDB"
cn.Open "Data Source = <SQL Server Name>", "testlogin"

Set cmd.ActiveConnection = cn
cmd.CommandType = &H0004
cmd.CommandText = "mainproc"
cmd.CommandTimeout = 0

' Prepare the command
cmd.Prepared = True
     
Set rs = cmd.Execute
Wscript.echo rs(0)
cn.Close

Set cmd = Nothing
Set cn = Nothing

Run the Microsoft Visual Basic Test.vbs script file.