sp_restore_system_role

Restores the system defined role or database owner to the default role privilege configuration.

Syntax

sp_restore_system_role [role_name [, all_dbs]]

Parameters

Examples

Usage

There are additional considerations when using sp_restore_system_role:
  • sp_restore_system_role restores a system-defined role, user-defined role sa_serverprivs_role, or database owner to the default role privilege configuration. The allowed system-defined roles include: sa_role, sso_role, oper_role, replication_role, and keycustodian_role. For the list of privileges granted to the above roles or database owner in the default role privilege configuration, see Using Granular Permissions in the Security Administration Guide.

  • When you specify all_dbs, the restoration operation does not apply to sybsecurity database. You need to manually restore privileges of the role or database owner in sybsecurity if needed.

Permissions

The permission checks for sp_restore_system_role differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage server permissions privileges to restore sa_role, and a user with manage security permissions to restore other roles or the database owner. To use all_dbs option, you also need to have use any database privilege.

Disabled

With granular permissions disabled, you must be a user with sa_role to restore sa_role, and a user with sso_role to restore other roles and the database owner.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

81

Audit option

If dbcc auditing is on

Command or access audited

Execution of a dbcc command

Information in extrainfo
  • Roles – Current active roles

  • Keywords or optionsupgd_grantrev_sysrole_perms

  • Previous value – NULL

  • Current value – NULL

  • Other information – parameter list

  • Proxy information – Original login name, if set proxy in effect