Sometimes modifying stored procedures does not keep object rights

[GoogleCodeExporter]

What steps will reproduce the problem?
1. open a stored procedure
2. edit it
3. run it in its tab (so it will update the sp)

What is the expected output? 
the sp is updated, user rights to procedure should be kept

What do you see instead?
user rights are lost

What version of the product are you using? 
AseIsql trunk 199

On what operating system?
Windows Vista home premium 32bit

Please provide any additional information below.
This does not happen in every stored procedure, right now I haven't figured
out specific cases.  I remember this happening since trunk 186

Original issue reported on code.google.com by juliocro...@gmail.com on 9 Nov 2009 at 4:12

[GoogleCodeExporter]

I can't reproduce this problem in revision 199
You can test what kind of rights are generated for your procedure:
- select desired SP
- rightclick on it 
- select "Object rights" menu

if resulted sql does not contains some rights, then describe here which rights 
are
absent.

Original comment by daggett....@gmail.com on 9 Nov 2009 at 5:24

• Changed state: Feedback

[GoogleCodeExporter]

Specifically execute.

Original comment by juliocro...@gmail.com on 9 Nov 2009 at 5:41

[GoogleCodeExporter]

I have right now one SP with this problem.

When I do right click > Object rights
grant execute on dbo.sp_proforma_buscador to posuser /*dbo*/

when I modify the sp the same query returns no value.

When I grant the exec right, then the query will return again the "grant 
execute ..."

Original comment by juliocro...@gmail.com on 9 Nov 2009 at 5:57

[GoogleCodeExporter]

this sp looks something like this:

   create proc sp_proforma_buscador
   (@parameters .... .) as

   declare @cadena varchar(1000)

   set @cadena = " [statements] "

   exec (@cadena)

This is not the first time I use an SP this way... but since trunk 168 I am 
having
problems with this.

Original comment by juliocro...@gmail.com on 9 Nov 2009 at 6:00

[GoogleCodeExporter]

Still can't reproduce. I need more information.
I will provide a new menu item "Show header & footer"
So you can check header and footer for each procedure.

Original comment by daggett....@gmail.com on 9 Nov 2009 at 6:19

[GoogleCodeExporter]

Sometimes it happens that the issue is fixed when I drop the procedure, then
re-create it ... sometimes that doesn't work

Original comment by juliocro...@gmail.com on 9 Nov 2009 at 6:39

[GoogleCodeExporter]

Add this line in the file "stubs.sql".

print 'Database: [%PARM:Database%], Object name: [%PARM:Object name%], 
Type=[Procedure]'

And see if the owner is correct.

The default user is dbo.

Original comment by zluis0@gmail.com on 9 Nov 2009 at 7:02

[GoogleCodeExporter]

The code looks as follows:

--<Menu\Procedure header>--
print 'Database: [%PARM:Database%], Object name: [%PARM:Object name%], 
Type=[Procedure]'
go
use %PARM:Database%
go

if object_id('%PARM:Object name%') is not null
    drop procedure %PARM:Object name%
go

Original comment by zluis0@gmail.com on 9 Nov 2009 at 7:04

[GoogleCodeExporter]

The owner is dbo.

Original comment by juliocro...@gmail.com on 9 Nov 2009 at 7:28

[GoogleCodeExporter]

When I get some more specific info I'll write

Original comment by juliocro...@gmail.com on 9 Nov 2009 at 7:29

[GoogleCodeExporter]

Please use the last build (trunk 205)
There is a new menu item "Show Object Header & Footer"
This displays header & footer for the current object (procedure)

Those will be exact sql to be executed on compile.
If you will detect empty footer when it should be, try to describe this.
Try to reproduce it yourself with the same object.

Original comment by daggett....@gmail.com on 9 Nov 2009 at 9:58

[GoogleCodeExporter]

Excellent, I'll keep you posted if I can reproduce the error.

Somehow the sp's I was having trouble with are working just fine. (weird)

Original comment by juliocro...@gmail.com on 9 Nov 2009 at 10:15

[GoogleCodeExporter]

GOT IT!!

1- create stored procedure (do not assign rights)
2- run your whatever program you are running
   2.1 - you'll get the "EXECUTE permission denied on object ..." error
3- grant execute permission on object
   3.1 - Whithout closing aseiql or the sp window run again your program .. should be
fine.
4- return to aseisql and make a change in the sp
5- run again your program
   5.1 - VOILA!! You'll get the error again.

Logically, if the header/footer of the sp is not aware of the new permissions 
to the
object before alter proc is run, it will allways loose them on update.

Original comment by juliocro...@gmail.com on 12 Nov 2009 at 3:52