Re: pass stored procedure parameters in asp

Well then, all I can suggest is using SQL Profiler to run a trace and make
sure the expected user accountis being used to connect to the database, and
that the user is successfully logged in, and that the correct procedure name
is being called.


c676228 wrote:
Yes, I did log in as bettys and execute voidTran, no problem at all.

In Management Studio, open a new query window, logging in with the
bettys account and try to execute the procedure.

Alternatively, you can use the EXECUTE AS statement to test the
ability of the bettys account to run the procedure, like this:

EXECUTE AS User='bettys'
exec voidTran ...
REVERT --revert to your own context


c676228 wrote:
Bob,
I got the script from the mgment studio and bettys already under dbo
schema. CREATE USER [bangaltiger] FOR LOGIN [bettys] WITH
DEFAULT_SCHEMA=[dbo]

And I looked at the properties(mgment studio, right click on stored
procedure and properties) of voidTran stored procedure and other
procedures, there isn't anything different. That's bizzare.

And I did the following:
use mydatabase
go
grant execute on dbo.voidTran to bettys

still the same error message.



No. you grant Execute permission to the user for this procedure the
same way you grant permissions for the other procedures.

Here is the T-SQL statement to do so:

Use NameOfYourDatabase
GO
GRANT Execute For dbo.voidTran NameOfUser

This just occurred to me: what is the default schema for
NameOfUser? If it is not "dbo", change it to "dbo":

ALTER USER NameOfUser WITH DEFAULT_SCHEMA = dbo;


c676228 wrote:
Hi Bob,

I believe it is the permission issue.
For now the only thing I can think of is
voidTran is a stored procedure for deleting records from the
database. While any other stored procedures I don't have any
problems are just insert or update records in the database.

Maybe there is a special permission needed for deleting records?


It's permissions...
Or, you did not create the procedure where you thought you did
...

c676228 wrote:
Bob,

But I use the exact same connection string, it doesn't have any
problems to execute other stored procedures. All those
procedures belong to dbo including voidTran.
so I don' have any clues.

Then you have a permissions problem. The user account being
used in your connection string has not been granted permission
to execute
that procedure.

c676228 wrote:
Hi Bob,
Nice to hear from you again.
I tried. the error is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find
stored procedure 'voidTran'.

but voidTran stored procedure is there.
--
Betty


"Bob Barrows" wrote:

c676228 wrote:
Now I chnaged my code to:
cmdTemp.CommandText = "voidTran" 'stored procedure for exec
void cmdTemp.CommandType = adCmdStoredProc
cmdTemp.Parameters.Append
cmdTemp.CreateParameter("productName", adChar, adParamInput)
cmdTemp.Parameters.Append
cmdTemp.CreateParameter("tranNumber", adChar , adParamInput)
'attach store procedure parameter
cmdTemp("productName")= "AC"
cmdTemp("tranNumber")= "VKYF68483010"
cmdTemp.Execute

and the error message is like this:

You have no output parameters. Just call your procedure like
this:

conn.voidTran "AC", "VKYF68483010"

Make sure the user account being used by the connection has
rights to that stored procedure.

--
HTH,
Bob Barrows

--
HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap
so
I don't check it very often. If you must reply off-line, then
remove
the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap
so
I don't check it very often. If you must reply off-line, then
remove the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: pass stored procedure parameters in asp
    ... you can use the EXECUTE AS statement to test the ... procedure and properties) of voidTran stored procedure and other ... This email account is my spam trap ...
    (microsoft.public.inetserver.asp.general)
  • Re: pass stored procedure parameters in asp
    ... In Management Studio, open a new query window, logging in with the bettys ... account and try to execute the procedure. ... procedure and properties) of voidTran stored procedure and other ...
    (microsoft.public.inetserver.asp.general)
  • Re: pass stored procedure parameters in asp
    ... Yes, I did log in as bettys and execute voidTran, no problem at all. ... account and try to execute the procedure. ... procedure and properties) of voidTran stored procedure and other ...
    (microsoft.public.inetserver.asp.general)
  • Re: SQL Server 2005 Stored Procedure security annoyances
    ... Can you grant an EXECUTE permission on stored procedure? ... I have came to the maturity level to stop using the sa account when I ... I mean it is unrealistic to have to manually set for each SP the rights ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server Agent jobs - The specified @job_name does not exist.
    ... > NETWORK SERVICE as the ASP.NET account. ... > a stored procedure in the database which attempts to use ... > The only way I can get the procedure to execute is by ... > login and cannot be selected as the job owner? ...
    (microsoft.public.sqlserver.security)
Loading