Question on Stored procedure

Database
Enthusiast

Question on Stored procedure

This is my first  attempt to write a  stored procedure in Teradata. I am getting an authorization error:

The user does not have CREATE PROCEDURE access to database pmehrotr

  1. I presume a user who wants to create procedure need CREATE PROCEDURE authorization in each database in which stored procedure is being created.
  2. Does create procedure provide authorization to create function, macro as well..
  3. Does create procedure require permanent space in the database it is being created in. I know views do not require permanent space.

Thanks.

2 REPLIES
Enthusiast

Re: Question on Stored procedure

Hi,

Did you check dbc.allrights table?

SELECT UserName FROM dbc.AllRights 

WHERE DatabaseName ='yourdb...' AND TableName ='....'

 AND AccessRight = '..' 

GRANT create ,....EXECUTE,.... PROCEDURE ON '....' TO xxx 

Once you get into dbc.allrights, it will answer many questions for permissions.

Views, Macros and Triggers don't require Perm space.

 

Cheers,

Raja

 


Junior Contributor

Re: Question on Stored procedure

Q1: of course. CREATE PROCEDURE is not part of the default rights granted to a user, it must be granted explicitly by the DBA.

Q2: of course not. There are seperate rights for all CREATEs

Q3: yes, typically a few kilobytes (but skewed)