If i delete all the objects of the database and then drop the database are the rights above also deleted from the relevant DBC tables or are they remain orphaned there forever??
They are usually dropped but some may get orphaned.
You can identify with this query.
The GSC will help if cleanup is needed.
CAST(ta.count1 AS BIGINT) (TITLE 'NO OBJ count'),
CAST(tb.count2 AS BIGINT) (TITLE 'NO DB count'),
CAST(tc.count3 AS BIGINT) (TITLE 'NO USR/ROLE count'),
CAST(td.count4 AS BIGINT) (TITLE 'Total count'),
(ta.count1/td.count4)*100 (TITLE 'NO OBJ %', FORMAT '----9.99'),
(tb.count2/td.count4)*100 (TITLE 'NO DB %', FORMAT '----9.99'),
(tc.count3/td.count4)*100 (TITLE 'NO USR/ROLE %', FORMAT '----9.99')
(SELECT CAST(COUNT(*) AS FLOAT) as count1
FROM DBC.AccessRights a1
WHERE a1.TVMID <> '000001000000'xb
AND NOT EXISTS (SELECT 1 FROM DBC.TVM t1
WHERE t1.tvmid = a1.tvmid)) ta,
(SELECT CAST(COUNT(*) AS FLOAT) as count2
FROM DBC.AccessRights a2
WHERE NOT EXISTS (SELECT 1 FROM DBC.DBase d2
WHERE d2.databaseid = a2.databaseid)) tb,
(SELECT CAST(COUNT(*) AS FLOAT) as count3
FROM DBC.AccessRights a3
WHERE NOT EXISTS (SELECT 1 FROM DBC.DBase d3
WHERE d3.databaseid = a3.userid)
AND NOT EXISTS (SELECT 1 FROM DBC.Roles r3
WHERE r3.roleid = a3.userid)) tc,
(SELECT CAST(COUNT(*) AS FLOAT) as count4 FROM DBC.AccessRights) td;
# chmod +x check.sh
# ./check.sh 2>&1|tee check.sh.out
NO OBJ count 0
NO DB count 0
NO USR/ROLE count 0
Total count 23363
NO OBJ % 0.00 - ok. If > 10%, please escalate.
NO DB % 0.00 - ok. If > 10%, please escalate.
NO USR/ROLE % 0.00 - ok. If > 10%, please escalate.
Also if i have a data and dictionary backup of the dropped database descibed above and i use it to COPY(using ARCMAIN) the data to a new database will the rights also be copied??
If you use DROP and DELETE DATABASE the rights will always be dropped from the dictionary as well. Orphan rights occur when dictionary restores are done but some of the objects are not restored.