Adding rights for use DDL in procedure

Database
Max
Enthusiast

Adding rights for use DDL in procedure

Hi all,

first of all, thanks in advance... ;-)

This is my problem: my user is granted for create,drop,select,insert and so on but when I would drop a existing table into a stored procedure I receive this message:

SPL1078:E(L127), User is not authorized to use the DDL/DCL/Dynamic SQL statement.

Without the drop statement, stored procedure is created successfully...

I've just grant my user with GRANT ALL ON [Database_Name] TO [UserName]

...

2 REPLIES
Senior Apprentice

Re: Adding rights for use DDL in procedure

Hi Max,
due to security reasons the creator and the owner of the SP *must* be the same, if you want to submit any DDL or Dynamic SQL,
i.e. User xy submits "create procedure xy.spname as ..."

Check the manuals for details:
SQL Reference: Stored Procedures and Embedded SQL
Chapter 4: SQL Stored Procedures
Rules for Using SQL Statements in Stored Procedures

Dieter
Max
Enthusiast

Re: Adding rights for use DDL in procedure

Thanks Dieter,

You are right ! I solve my problem too !!!!