SYSUSERAUTHORITY compatibility view (deprecated)

The SYSUSERAUTHORITY view is provided for compatibility with older versions of the software. Use the SYSROLEGRANTS consolidated view instead.

Each row of SYSUSERAUTHORITY system view describes an authority granted to one user ID.

Note: Although the title of this view contains the word authority, the security model is based on roles and privileges. The data in the view is therefore compiled using role information from the tables and views mentioned in the view definition.

ALTER VIEW "SYS"."SYSUSERAUTHORITY" as
  select ISYSROLEGRANT.grantee as user_id,
    sp_auth_sys_role_info.auth
    from SYS.ISYSROLEGRANT
      natural join dbo.sp_auth_sys_role_info()
    where ISYSROLEGRANT.grant_type <> (0x02|0x04) and
    not ISYSROLEGRANT.grantee = any(select sp_auth_sys_role_info.role_id from dbo.sp_auth_sys_role_info()) union
  select ISYSUSER.user_id,
    cast('GROUP' as varchar(20)) as auth
    from SYS.ISYSUSER
    where ISYSUSER.user_name in( 'SYS','PUBLIC','diagnostics','SYS_SPATIAL_ADMIN_ROLE','rs_systabgroup','SA_DEBUG','dbo' ) union
  select ISYSUSER.user_id,
    cast('GROUP' as varchar(20)) as auth
    from SYS.ISYSUSER
    where ISYSUSER.user_type = (0x02|0x04|0x08) union
  select cast(opt.setting as unsigned integer) as user_id,
    cast('PUBLISH' as varchar(20)) as auth
    from SYS.ISYSOPTION as opt
    where opt."option" like '%db_publisher%' and opt.setting not like '%-1%'