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.

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"


.

Relevant Pages

  • 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: pass stored procedure parameters in asp
    ... bettys account and try to execute the procedure. ... 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: 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)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > I logged in as that user, tried to execute the DTS ... which then launches the DTS package using the sp_OA* procs? ... account is used during proc execution. ... > proxy account to use in the Job Systems tab of SQL Server Agent ...
    (microsoft.public.sqlserver.security)
Loading