473,323 Members | 1,560 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,323 software developers and data experts.

SQL procedure permissions

I created and tested a stored SQL procedure in our test database.

When I add this stored SQL procedure to our production database our
users get errors saying that they don't have permission to execute
object.
(Note that this procedure already existed without any code in its body
- I have written code in its body, dropped and re-added the procedure)

I have also tried to give everyone permission to execute the procedure
using the below statement: GRANT EXECUTE ON PACKAGE LYNX.P5344550 TO
USER ALL;

However, This didn't fix the problem.

Is there any way that I can grant access for all users to execute this
procedure? Or Is there any way that I can specify permissions on the
procedure to execute with an administrative user rights?

Dec 6 '05 #1
4 4285
In article <11*********************@f14g2000cwb.googlegroups. com>,
Karl (ka******@hotmail.com) says...

I have also tried to give everyone permission to execute the procedure
using the below statement: GRANT EXECUTE ON PACKAGE LYNX.P5344550 TO
USER ALL;


Check the GRANT commands at http://tinyurl.com/csbfj and
http://tinyurl.com/ave8m
Dec 6 '05 #2
In DB2 version 8 you grant execute on the procedure, not the package.
This can be done with the Control Center also.

Dec 6 '05 #3
There is a DYNAMICRULES=BIND option you must set correctly when
building the procedure. If this is not set correctly, it is not enough
with execute privilege on the procedure, but the users must also have
the correct permissions on the underlyinig objects.

Regards
Odd B. Andersen
ErgoGroup AS, Oslo, Norway

Dec 7 '05 #4
sorry for the lateness of this reply, I looked up the correct syntax at
the site you recommended and it worked.
GRANTE EXECUTE ON PACKAGE LYNX.XXXXX TO PUBLIC is what I was trying to
do - the USER ALL was completely wrong.
Thankyou very much.

Dec 7 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

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...
1
by: Martin Feuersteiner | last post by:
Dear Group I'm having two stored procedures, sp_a and sp_b Content of stored procedure A: CREATE PROCEDURE dbo.sp_a SELECT * FROM a GO Content of stored procedure B:
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...
1
by: timc | last post by:
We are running SQL Server 2000 Developer Edition. I don't want to make the developers the sysadmin or even the dbo in the user databases. Is there a way to give them access to only view the...
2
by: sbw | last post by:
Hi. I have an app which uses multiple databases and I would like to know if there is a way to connect to different databases through OLEdb (or any other connection string) which allows me to...
3
by: N. Shamsundar | last post by:
A stored procedure (listed below) that loads fine on Windows XP with DB2 V8.1.4 Express fails to load on Linux DB2 Workgroup server V8.1, with the following message: > sh-2.05a$ db2 -td@ -f...
2
by: Not Me | last post by:
Hey, Coming back to a piece of work I did a while back, which used a stored procedure to populate a list box. The SP takes a single parameter, and I think this is the reasoning for using 'exec'...
2
by: masri999 | last post by:
I have a requirement in SQL 2005 in Development database 1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) . 2. Only DBA's ( who are database owners ) can create, alter tables ....
0
by: esmith2112 | last post by:
Is there any way to retain the permissions previously granted on a stored procedure through an explicit "GRANT EXECUTE" statement, after recompiling said procedure? We are using group permissions...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.