creating DB from old DB

Database
Enthusiast

creating DB from old DB

Hi Team,

 

Below is senario need to do.

---------------------------------

“When drop function fails with a 2981 error  then a DELETE DATABASE <databasename> is the only workaround.

Please be sure to be able to recreate all other objects in another database before DELETE DATABASE is performed.

Once delete database completes you can copy the objects back to the original database.”

--------------------------------

 

Here i am moving the objects from EDW_PPI into EDW_PPI_COPY except for the functions,

I am copy all the tables/views etc but not the functions

then , i am droping the EDW_PPI and 

then re create EDW_PPI and copy the objects back from EDW_PPI_COPY.

 

Here my doubt is,

1)  Need to check permissions/roles/rolegroups on EDW_PPI and replicate it to EDW_PPI_COPY and the new EDW_PPI ?

 

if yes , what is the process to copy th epermissons/roles/rolegroups , how to find all this by query?

 

 

 

 

 

1 REPLY
Teradata Employee

Re: creating DB from old DB

You'll need to query dbc.AllRightsV and dbc.AllRoleRightsV to extract the information needed to re-GRANT permissions.

There's SQL you could adapt in Appendix B of the Security Administration manual, though the example macro:

  • filters for a single user (you'd want to include all UserNames)
  • excludes automatic rights (where CreatorName = UserName) which you might need to reinstate
  • includes rights from nested roles (which are not directly granted - so you would only need the "User" and "Member" subqueries)