Fastpath has written about minimizing the ‘sa’ user in the past This led to more people using Power User. They’ve also done a good job of highlighting that Power User really isn’t a role, it’s an override. If a user is a Power User the system effectively skips the security check. This also means that Power User doesn’t appear as a user with access to specific items when you run security reports. That can be a problem for the auditors. Also, you can’t limit what a Power User can do.
One option is to create your own Super User, and while it’s not hard, there are a few quirks.
1) Clear the SY09400 table. Victoria Yudin has specifics.
This repopulates the table with the current security resources. There is additional code that runs if the newest version of the Support Debugging Tool is installed to add items beyond just forms and reports.
2) Run the SQL Script below. This adds a Super User role and assigns security to all of the items, even if the description hasn’t been added to the SY09400 table. I still can’t swear that this catches everything, but it should and it worked in testing. Worst case, it’s a whole lot easier to start with this than to check everything manually. You’ll still need to assign users to this role, but now it’s a real role, You can report off of this role and remove specific access as necessary.
–Create SUPERUSER Task
Insert into SY09000 (SECURITYTASKID,SECURITYTASKNAME,SECURITYTASKDESC,SECURITYTASKCATEGORY,DEFSECTASK,CRUSRID,CREATDDT,MDFUSRID,MODIFDT)
Values (‘SUPERUSER’,’SUPERUSER’,’Super User task to replace Power User role’,7,0,’sa’, cast(getdate() as date), ”, ‘1/1/1900’)
–Assign all security items to SUPERUSER task
Insert Into SY10700 (SECURITYTASKID,DICTID,SECURITYID,SECRESTYPE)
Select distinct ‘SUPERUSER’,a.DICTID,a.SECURITYID,a.SECRESTYPE from
(select distinct DICTID,SECURITYID,SECRESTYPE from SY10700
select distinct DICTID,SECURITYID,SECRESTYPE from SY09400) a
–Create SUPERUSER Role
Insert into SY09100 (SECURITYROLEID,SECURITYROLENAME,SECURITYROLEDESC,SECROLETYPE,CRUSRID,CREATDDT,MDFUSRID, MODIFDT)
Values (‘SUPERUSER’,’SUPERUSER’,’Super User Role to replace Power User role’,2,’sa’,cast(getdate() as date), ”, ‘1/1/1900’)
–Assign SUPERUSER Task to SUPERUSER Role
Insert into SY10600 (SECURITYROLEID,SECURITYTASKID)