afh@stc06.ornl.gov (HUNTLEY A F) (01/03/91)
There is a certain type of stored procedure which needs to be MANUALLY
dropped and recreated when a Sybase database is reloaded from a dump
(Sybase bug number 13321). The necessary conditions for the problem to
occur are:
1) have stored procedures that use both temporary tables (tempdb
tables), and permanent tables in the user database
2) allow these stored procedures to be executed by users who have no
permission to create user database tables (non-temporary tables)
All stored procedures that create temporary tables will fail upon execution
after EITHER of the following has occurred:
1) when a database dump has been reloaded (either to a new space
allocation, to a new dataserver, or simply in restoring the
database from a backup)
2) if a permanent table that is also used by the stored procedure
has been dropped and recreated (even with no structure change)
This causes the intermittent and (apparently) unpredictable failure of stored
procedures. The following error message is usually received:
Msg 262, Level 14, State 1: CREATE TABLE permission denied
Under these circumstances, all stored procedures that create temporary tables
will fail repeatedly when they are executed by a user who has no permission
to create permanent tables. Usually the error message above is received,
however I believe we have also noted times when the failure caused the
dataserver to go into a loop, requiring that the dataserver be restarted.
Dropping and recreating the stored procedure, and regranting execution
permission on the stored procedure solves the problem.
We have found the bug on a Sun 4, but I believe it is a generic bug. I have
been told that the bug will not be fixed until release 4.2.1 (which I assume
is at least one year off). From the bug number I assume that the bug has
been known for at least one year as well.
The problem can easily be avoided by recreating all such stored procedures
when either of the situations described above occurs. Note that simple
recompilation of the affected stored procedures is insufficient.
Al Huntley, afh@ornl.gov
(615) 576-7170
Martin Marietta Energy Systems
Oak Ridge, TN
Opinions expressed are those of the author and NOT his employer.