stored procedure denies EXECUTE permission

[Zeke Hoskin]

Suddenly a stored procedure, very much like several others, is giving
EXECUTE permission denied on object 'Add_Adjustment', database
'InStab', owner 'zhoskin'.
server:Msg 229, Level 14, State 5, Procedure Add_Adjustment, Line 18.

I'm zhoskin. I am the dbo and created the procedure, and when I look
at its properties, I have EXEC permission. Line 18 is just the return
statement. The values are all appropriate for the table. So what is
usually going on when a stored procedure denies access to its owner?

Thanks//Zeke Hoskin
CREATE Procedure Add_Adjustment (@AdjAcc Int, @AdjType Char, @AdjAmt
Money, @AdjYrMth Int, @AdjDate Datetime)
/* Add a Dep Adj (Type Z, Negative) or WD Adj (type Y, Positive) */
AS
IF @AdjType = 'Z'
BEGIN
Insert Into tblTxn(TxnAcc, TxnType, TxnAmt, TxnSign, TxnYrMth,
TxnDate)
VALUES(@AdjAcc, @AdjType, @AdjAmt, -1, @AdjYrMth, @AdjDate)
END

IF @AdjType = 'Y'
BEGIN
Insert Into tblTxn(TxnAcc, TxnType, TxnAmt, TxnSign, TxnYrMth,
TxnDate)
VALUES(@AdjAcc, @AdjType, @AdjAmt, 1, @AdjYrMth, @AdjDate)
/*this is just to afect line numbers*/
END
/* set nocount on */
/*space holder*/
return
/*more space*/
GO

[mountain man]

"Zeke Hoskin" <ze**@zekehoskin.com> wrote in message
news:77**************************@posting.google.c om...

Suddenly a stored procedure, very much like several others, is giving
EXECUTE permission denied on object 'Add_Adjustment', database
'InStab', owner 'zhoskin'.
server:Msg 229, Level 14, State 5, Procedure Add_Adjustment, Line 18.

I'm zhoskin. I am the dbo and created the procedure, and when I look
at its properties, I have EXEC permission. Line 18 is just the return
statement. The values are all appropriate for the table. So what is
usually going on when a stored procedure denies access to its owner?

Thanks//Zeke Hoskin
CREATE Procedure Add_Adjustment (@AdjAcc Int, @AdjType Char, @AdjAmt
Money, @AdjYrMth Int, @AdjDate Datetime)
/* Add a Dep Adj (Type Z, Negative) or WD Adj (type Y, Positive) */
AS
IF @AdjType = 'Z'
BEGIN
Insert Into tblTxn(TxnAcc, TxnType, TxnAmt, TxnSign, TxnYrMth,
TxnDate)
VALUES(@AdjAcc, @AdjType, @AdjAmt, -1, @AdjYrMth, @AdjDate)
END

IF @AdjType = 'Y'
BEGIN
Insert Into tblTxn(TxnAcc, TxnType, TxnAmt, TxnSign, TxnYrMth,
TxnDate)
VALUES(@AdjAcc, @AdjType, @AdjAmt, 1, @AdjYrMth, @AdjDate)
/*this is just to afect line numbers*/
END
/* set nocount on */
/*space holder*/
return
/*more space*/
GO

What about permissions set on the data table(s)
or the ownership of same?


--
Pete Brown
Winluck P/L
IT Managers & Engineers
Falls Creek
Australia
www.mountainman.com.au/software

[Simon Hayes]

"Zeke Hoskin" <ze**@zekehoskin.com> wrote in message
news:77**************************@posting.google.c om...

Suddenly a stored procedure, very much like several others, is giving
EXECUTE permission denied on object 'Add_Adjustment', database
'InStab', owner 'zhoskin'.
server:Msg 229, Level 14, State 5, Procedure Add_Adjustment, Line 18.

I'm zhoskin. I am the dbo and created the procedure, and when I look
at its properties, I have EXEC permission. Line 18 is just the return
statement. The values are all appropriate for the table. So what is
usually going on when a stored procedure denies access to its owner?

Thanks//Zeke Hoskin
CREATE Procedure Add_Adjustment (@AdjAcc Int, @AdjType Char, @AdjAmt
Money, @AdjYrMth Int, @AdjDate Datetime)
/* Add a Dep Adj (Type Z, Negative) or WD Adj (type Y, Positive) */
AS
IF @AdjType = 'Z'
BEGIN
Insert Into tblTxn(TxnAcc, TxnType, TxnAmt, TxnSign, TxnYrMth,
TxnDate)
VALUES(@AdjAcc, @AdjType, @AdjAmt, -1, @AdjYrMth, @AdjDate)
END

IF @AdjType = 'Y'
BEGIN
Insert Into tblTxn(TxnAcc, TxnType, TxnAmt, TxnSign, TxnYrMth,
TxnDate)
VALUES(@AdjAcc, @AdjType, @AdjAmt, 1, @AdjYrMth, @AdjDate)
/*this is just to afect line numbers*/
END
/* set nocount on */
/*space holder*/
return
/*more space*/
GO

One possibility is that someone DENYed execute to public - since all users
are members of the public role, this would explain the error. You can check
the output of this command:

exec sp_helprotect zhoskin.Add_Adjustment

Another possibility is that you think you're connected as zhoskin, but in
fact you're not (perhaps due to using an intermediate application or an
application role). You can use the USER_NAME() function to check.

Simon

[Zeke Hoskin]

"Simon Hayes" <sq*@hayes.ch> wrote in message news:<40**********@news.bluewin.ch>...

"Zeke Hoskin" <ze**@zekehoskin.com> wrote in message

Suddenly a stored procedure, very much like several others, is giving
EXECUTE permission denied on object 'Add_Adjustment', database

One possibility is that someone DENYed execute to public - since all users
are members of the public role, this would explain the error. You can check
the output of this command:

exec sp_helprotect zhoskin.Add_Adjustment

That was it! Thanks evr so//Zeke