SYSUSERAUTHORITY 兼容性视图（不建议使用）

提供 SYSUSERAUTHORITY 视图的目的是为了与旧版本软件兼容。改用 SYSROLEGRANTS 统一视图。

SYSUSERAUTHORITY 系统视图的每行描述授予一个用户 ID 的一个权限。

注意

尽管该视图的标题包含文字 authority，但是安全模式仍基于角色和特权。因此视图中的数据使用来自视图定义中所提及表和视图中的角色信息进行编译。

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%'

另请参见

使用DocCommentXchange讨论此页。