471,066 Members | 1,247 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

stored procedure denies EXECUTE permission

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
Jul 20 '05 #1
3 12902
"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


Jul 20 '05 #2

"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
Jul 20 '05 #3
"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
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Michael | last post: by
6 posts views Thread by Martin Feuersteiner | last post: by
1 post views Thread by Purple-Man | last post: by
2 posts views Thread by Dino L. | last post: by
3 posts views Thread by John Dohn | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.