Authorities become compatibility roles

When you upgrade a database, users that were granted authorities in pre-16.0 databases are automatically granted an equivalent compatibility role for that authority, with one exception: The REMOTE DBA authority actually becomes the SYS_RUN_REPLICATION_ROLE system role. It is included in the table below to show what the REMOTE DBA authority became.

If a user had the ability to administer the previous authority, the user has the ability to administer the compatibility role.

Compatibility roles are not modifiable; however you can migrate them to a user-defined role, and then modify them. Migrating compatibility roles is simple, and restoring them later is also simple. When you migrate a compatibility role to a user-defined role, all users that were granted the compatibility role are automatically granted the new user-defined role. The compatibility role is automatically dropped once it has been migrated. However, you can restore compatibility roles using the CREATE ROLE statement.

Backwards compatibility for SQL statements has been provided so applications that grant or revoke authorities continue to work. However, the old syntax is deprecated and you should consider changing your applications to use the new SQL syntax for roles.

The following table shows the pre-16.0 authorities and the roles they become when a database is upgraded.

Pre-16.0 authority	Equivalent role	Description
ALL	SYS_AUTH_RESOURCE_ROLE compatibility role	Allows a user to create database objects, such as tables, views, stored procedures, and triggers.
BACKUP	SYS_AUTH_BACKUP_ROLE compatibility role	Allows a user to back up databases and transaction logs with archive or image backups by using the BACKUP statement or dbbackup utility.
DBA	SYS_AUTH_DBA_ROLE compatibility role SYS_AUTH_SA_ROLE compatibility role SYS_AUTH_SSO_ROLE compatibility role	Allows users to perform all possible privileged operations. Users with the SYS_AUTH_DBA_ROLE system role can create database objects and assign ownership of these objects to other user IDs, change table structures, create new user IDs, revoke permissions from users, back up the database, and so on. Of the possible privileged operations that the SYS_AUTH_DBA_ROLE compatibility role can perform, the SYS_AUTH_SA_ROLE compatibility role allows the user to perform all database administration-related activities, such as creating tables, and backing up data. Of the possible privileged operations that the SYS_AUTH_DBA_ROLE compatibility role can perform, the SYS_AUTH_SSO_ROLE compatibility role allows the user to perform the security and access-related administration activities, such as creating users, and granting privileges on objects.
PROFILE	SYS_AUTH_PROFILE_ROLE compatibility role	Allows a user to perform profiling, tracing, and diagnostic operations.
READCLIENTFILE	SYS_AUTH_READCLIENTFILE_ROLE compatibility role	Allows a user to read files on the client computer, for example when loading data from a file on a client computer.
READFILE	SYS_AUTH_READFILE_ROLE compatibility role	Allows a user to use the OPENSTRING clause in a SELECT statement to read a file.
REMOTE DBA	SYS_RUN_REPLICATION_ROLE system role	Allows a SQL Remote user to perform replication activities by using the dbremote utility, and a MobiLink user to perform synchronization activities by using the dbmlsync utility. It does not allow administration of replication, however. There is also a new system role, SYS_REPLICATION_ADMIN_ROLE you can grant to replication administrators. In pre-16.0 databases, replication administrators needed DBA authority to perform administrative tasks. The SYS_REPLICATION_ADMIN_ROLE system role encompasses the privileges needed to perform those administrative tasks.
RESOURCE	SYS_AUTH_RESOURCE_ROLE compatibility role	Allows a user to create database objects, such as tables, views, stored procedures, and triggers.
VALIDATE	SYS_AUTH_VALIDATE_ROLE compatibility role	Allows a user to perform database, table, index, and checksum validation by using the VALIDATE statement or dbvalid utility.
WRITECLIENTFILE	SYS_AUTH_WRITECLIENTFILE_ROLE compatibility role	Allows a user to write to files on a client computer, for example when using the UNLOAD TABLE statement to write data to a client computer.
WRITEFILE	SYS_AUTH_WRITEFILE_ROLE compatibility role	Allows a user to execute the xp_write_file system procedure.

Authorities become compatibility roles

See also