So I’m working with a client who asked me to clear all existing security in Planning, and I knew what exactly to look for. (and I know you also know how to do it….). I was like “Oh I can do this even while I’m sleeping”.
But there was a catch to it, they said they want to keep all Administrator users and remove others.
SL_CLEARALL, clears all the security!!!!!
I ran a query against HSP_ACCESS_CONTROL table (where security is getting stored) and thought of deleting it from Relational 🙂
So since it was my own setup I thought of playing around with the Relational database.
Please take backup of Planning relational database and dump of security (run ExportSecurity.cmd)
If you feel confident enough then read on 🙂
HSP_ACCESS_CONTROL table information
- USER_ID – from HSP_OBJECT (also present in HSP_USERS and HSP_GROUP)
- OBJECT_ID – from HSP_OBJECT (which object the security is defined)
- Launch=4 (you’ll get this if you are using Calc Manager)
This is the table where Planning security is stored.
I added some tasklists to the Adminstrators and added Security to Webforms and then was looking for a way to do this (how to check the object type and user type?).
HSP_USERS table contains the information of roles of users. (where as HSP_GROUP doesn’t have this, so the search will fail and you’ll have to delete all the groups)
So I just combined all these information and wrote a delete SQL statement
delete from hsp_access_control where user_id in (select user_id from hsp_users where role <> 0 and role <> 3) or
user_id in (select object_id from hsp_object where object_type = 6);
This one was to remove all security except Administrators, so you can modify it to remove everything except Webforms.
delete from hsp_access_control where object_id in (select object_id from hsp_object where object_type <> 7);
Do a commit and restart Planning, all the security assignment except for admins and owner is gone.