Restores the system defined role or database owner to the default role privilege configuration.
sp_restore_system_role [role_name [, all_dbs]]
One of sa_role, sso_role, oper_role, replication_role, keycustodian_role, sa_serverprivs_role, and dbo. A usage message will be displayed if no parameter is specified.
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.
Restore sso_role to the default role privilege configuration in all databases.
sp_restore_system_role sso_role, all_dbs
Restores sa_role to the default role privilege configuration in db1 only.
use db1
sp_restore_system_role sa_role
Restore dbo to the default privilege configuration in master.
use master
sp_restore_system_role dbo
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 all_dbs is specified, the restoration operation will 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.
Granular permissions 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. |
Granular permissions 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:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
81 |
If dbcc auditing is on |
Execution of a dbcc command |
|
