sp_restore_system

Syntax

sp_restore_system_role [role_name [, all_dbs]]

role_name – One of sa_role, sso_role, oper_role, replication_role, keycustodian_role, sa_serverprivs_role, and dbo. A usage message is displayed if no parameter is specified.
all_dbs – Restores the database owner or the role to the default role privilege configuration in all online databases. If all_dbs is not specified, only perform the change in the current database.

Example 1 – Restore sso_role to the default role privilege configuration in all databases.
```
sp_restore_system_role sso_role, all_dbs
```
Example 2 – Restores sa_role to the default role privilege configuration in db1 only.
```
use db1
```
```
sp_restore_system_role sa_role
```
Example 3 – Restore dbo to the default privilege configuration in master.
```
use master
```
```
sp_restore_system_role dbo
```

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.

Setting	Description
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.

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

Information	Values
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 options – upgd_grantrev_sysrole_perms Previous value – NULL Current value – NULL Other information – parameter list Proxy information – Original login name, if set proxy in effect