grant in a stored proc

I have a stored proc that create a table, renames it, then does a
grant on it. It runs without errors, but the table does not get the
permissions granted. Here is a small test that shows what I am talking
about:

1> create procedure lmTest as
2> begin
3> exec sp_rename 'lmTestTable2','lmTestTable3'
4> select * into lmTestTable1 from lmproc_Books
5> exec sp_rename 'lmTestTable1','lmTestTable2'
6> grant select on lmTestTable2 to public
7> end
8> go
1> lmTest
2> go
Object name has been changed.
Object name has been changed.
(return status = 0)
1> sp_helprotect lmTestTable2
2> go
grantor grantee type action object column grantable
------- ------- ---- ------ ------ ------ ---------

(1 row affected)
(return status = 0)

Why doesn't the grant work?

TIA!
-larry

On Aug 22, 9:54*am, "Larry.Mart...@gmail.com"
wrote:
> I have a stored proc that create a table, renames it, then does a
> grant on it. It runs without errors, but the table does not get the
> permissions granted. Here is a small test that shows what I am talking
> about:
>
> 1> create procedure lmTest as
> 2> begin
> 3> exec sp_rename 'lmTestTable2','lmTestTable3'
> 4> select * into lmTestTable1 from lmproc_Books
> 5> exec sp_rename 'lmTestTable1','lmTestTable2'
> 6> grant select on lmTestTable2 to public
> 7> end
> 8> go
> 1> lmTest
> 2> go
> Object name has been changed.
> Object name has been changed.
> (return status = 0)
> 1> sp_helprotect lmTestTable2
> 2> go
> *grantor grantee type action object column grantable
> *------- ------- ---- ------ ------ ------ ---------
>
> (1 row affected)
> (return status = 0)
>
> Why doesn't the grant work?
>
> TIA!
> -larry

This is a general problem that is often a gotcha when using sp_rename.

The query tree for stored procedures references objects by object id.
When you rename an object, the object id doesn't change, and the query
tree continues to refer to the original object. There is a warning
about
this in the Ref Manual entry for sp_rename that basically says (though
perhaps not as clearly) - "rebuild any compiled object that references
an object you have renamed before executing it again".

If the original object is dropped, the procedure should be able to re-
resolve
the query tree to pick up the new object with the same name. But as
long
as the original object id is around, the procedure will continue to
reference it.

You can somewhat work around that here by issuing the GRANT in
a dynamic sql call, as dynamic sql isn't parsed and compiled until it
is called,
and at that point the new t1 exists.

Try this:

create procedure lmTest as
begin
exec sp_rename 'lmTestTable2','lmTestTable3'
select * into lmTestTable1 from lmproc_Books
exec sp_rename 'lmTestTable1','lmTestTable2'
execute ("grant select on lmTestTable2 to public")
end
go
lmTest
go
sp_helprotect lmTestTable2
go

Note, however, that any other procedures you have out there that
reference lmTestTable2 should be rebuilt before they are executed
again.

On Aug 22, 11:51 am, Bret_Halford wrote:
> On Aug 22, 9:54 am, "Larry.Mart...@gmail.com"
>
>
>
> wrote:
> > I have a stored proc that create a table, renames it, then does a
> > grant on it. It runs without errors, but the table does not get the
> > permissions granted. Here is a small test that shows what I am talking
> > about:
>
> > 1> create procedure lmTest as
> > 2> begin
> > 3> exec sp_rename 'lmTestTable2','lmTestTable3'
> > 4> select * into lmTestTable1 from lmproc_Books
> > 5> exec sp_rename 'lmTestTable1','lmTestTable2'
> > 6> grant select on lmTestTable2 to public
> > 7> end
> > 8> go
> > 1> lmTest
> > 2> go
> > Object name has been changed.
> > Object name has been changed.
> > (return status = 0)
> > 1> sp_helprotect lmTestTable2
> > 2> go
> > grantor grantee type action object column grantable
> > ------- ------- ---- ------ ------ ------ ---------
>
> > (1 row affected)
> > (return status = 0)
>
> > Why doesn't the grant work?
>
> > TIA!
> > -larry
>
> This is a general problem that is often a gotcha when using sp_rename.
>
> The query tree for stored procedures references objects by object id.
> When you rename an object, the object id doesn't change, and the query
> tree continues to refer to the original object. There is a warning
> about
> this in the Ref Manual entry for sp_rename that basically says (though
> perhaps not as clearly) - "rebuild any compiled object that references
> an object you have renamed before executing it again".
>
> If the original object is dropped, the procedure should be able to re-
> resolve
> the query tree to pick up the new object with the same name. But as
> long
> as the original object id is around, the procedure will continue to
> reference it.
>
> You can somewhat work around that here by issuing the GRANT in
> a dynamic sql call, as dynamic sql isn't parsed and compiled until it
> is called,
> and at that point the new t1 exists.
>
> Try this:
>
> create procedure lmTest as
> begin
> exec sp_rename 'lmTestTable2','lmTestTable3'
> select * into lmTestTable1 from lmproc_Books
> exec sp_rename 'lmTestTable1','lmTestTable2'
> execute ("grant select on lmTestTable2 to public")
> end
> go
> lmTest
> go
> sp_helprotect lmTestTable2
> go
>
> Note, however, that any other procedures you have out there that
> reference lmTestTable2 should be rebuilt before they are executed
> again.

Works like a charm! Thanks much Bret!