473,405 Members | 2,262 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,405 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 13038
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: TJ Olaes | last post by:
Hello all, this is my second post to this newsgroup. It's a question about stored procedures and permissions and how these behave between databases. Here's the scenario. I have a database that...
3
by: Michael | last post by:
This one's really got me. I have a VB.NET (version 1.1.4322) project that provides an easy way to execute stored procedures on a generic level. When I run the code on computer A (running SQL...
1
by: Micah Gentry | last post by:
Is there any way to allow a user to use the xp_cmdshell extended stored procedure without giving that user execute permissions to xp_cmdshell in SQL server 6.5? Let me clarify. Lets say I (as the...
6
by: Martin Feuersteiner | last post by:
Dear Group I have found that table A had SELECT permissions for 'Public' but not table B. Giving 'Public' SELECT permissions on table B did the trick. HOWEVER, I don't want anyone to be able...
4
by: Robin Tucker | last post by:
Hi, I'm trying to determine with my program whether or not a given database supports a given feature set. To do this I'm querying for certain stored procedures in the sysobjects table and if...
1
by: Purple-Man | last post by:
wanted to use sp_OACreate, sp_OAMethod and sp_OADestroy to execute a DTS package from a stored procedure. I had the dba (using the sa account) create a wrapper stored procedure as recommended in...
1
by: aish | last post by:
I want to send email using sql stored procedure.my code is work fine in my local sqlserver account. when I use my online sql server it display this error. EXECUTE permission denied on object...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
3
by: John Dohn | last post by:
I can work with them just fine in VS but when I run my app, I get a select permission denied error on any view and execute permission denied on any SP in my web app. All was worked fine until...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

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.