Re: pass stored procedure parameters in asp

Tech-Archive recommends: Fix windows errors by optimizing your registry

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.


--
Betty


"Bob Barrows" wrote:

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"



.

Relevant Pages

  • Re: xp_logevent and service broker queues
    ... With EXECUTE AS OWNER 'dbo', the database owner of your user database needs to be 'sa' in order for the security context to be 'sa' in master too. ... This queue receives all messages and starts the stored procedure sp_AsyncReceive which is owned by the dbo. ... Maybe somebody can give me a hint how to call 'sysadmin' functions under the dbo user or how to change permissions for calling these functions in a stored procedure which is started by a service broker queue. ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... If 'select user' is returning DBO, it means you are connecting AS DBO, and ... testing the permissions the new user has. ... Are you saying that the permissions to create the stored procedure are ... Dan D. ...
    (microsoft.public.sqlserver.security)
  • Re: Stored Procedure Disappearing
    ... Make sure that the "Record Source Qualifier" is set to dbo; ... Sylvain Lafontaine, ing. ... Did you use dbo. ... stored procedure up into several smaller ones, or is there a better way ...
    (microsoft.public.access.reports)
  • Re: ADP: Cant use stored procedure on remote SQL server
    ... Not only I use dbo everywhere but I ... If you don't mention the owner when creating a new stored procedure, view, ... BTW in the database window, all the stored procedures are followed by ... Check also the owner of the SPInc stored procedure. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: deny truncate?
    ... appropriate command to the stored procedure script files. ... The sandbox approach would only apply for a very short development cycle. ... >> They do not need to be dbo to execute a dbo owned stored procedure. ... > I know, the thing is though, typing out all those statements could be> regarded as work that isn't actually getting anybody anwhere that they> couldn't be just put there by flipping a switch once. ...
    (microsoft.public.sqlserver.security)