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


sp_restore_system_role [role_name [, all_dbs]]




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.


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


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.


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.


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



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