About Dropping Users

Database
Teradata Employee

About Dropping Users

Hello All,

Regarding TD Adminstration, right now i am working on managing users, roles and access rights.   So i need to drop some un-used users but i can't, always getting error '3551: The user cannot DROP a database that has descendants' although i deleted ALL from the user first.

DELETE USER Testuser ALL; --> submitted correctly

DROP USER Testuser; ---> Error 3551  

I know i am missing a step or two but i can't find it.

Please advise on what's needed to be done.

 

Thanks in advance


Accepted Solutions
Senior Apprentice

Re: About Dropping Users

Hi,

 

Before you can drop a user (or database), it must be empty AND have no children.

 

Your "DELETE USER **bleep** ALL" only empties the database (removes tables, views etc.)

You have to remove children one by one, starting from the bottom of your hierarchy.

 

Also remember that any Join or Hash Indexes will have to be removed manually, the "DELETE...ALL" command typically won't get rid of them.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
3 REPLIES
Senior Apprentice

Re: About Dropping Users

Hi,

 

Before you can drop a user (or database), it must be empty AND have no children.

 

Your "DELETE USER **bleep** ALL" only empties the database (removes tables, views etc.)

You have to remove children one by one, starting from the bottom of your hierarchy.

 

Also remember that any Join or Hash Indexes will have to be removed manually, the "DELETE...ALL" command typically won't get rid of them.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: About Dropping Users

3551 The user cannot DROP a database that has descendants.
Explanation: The user tried to drop a database or user which had created other databases or users.
Generated By: OPT modules.
For Whom: The DBA and the End User.
Remedy: Drop the underlying databases or give them to another database. Resubmit the request.

DELETE USER drops all objects within a user, but not children of that user.

You probably don't want to drop those users/databases, so GIVE them to a different owner.

Teradata Employee

Re: About Dropping Users

Thank you dnoeth and Dave for your answers, it sheds the lights on what i was missing.

I need to drop all the children before dropping the owner.

Tags (1)