In SQL Server, fixed roles are predefined roles that provide specific permissions and access to users within a database. In a previous article, we discussed the Public role and the DataReader/DataWriter roles. In this article, we will explore the remaining fixed roles and their functionalities.

DB_AccessAdmin

The DB_AccessAdmin role allows users to delegate the ability to add or remove users from a specific database. Although the outdated stored procedures sp_adduser and sp_dropuser can be used, it is recommended to use sp_grantdbaccess and sp_revokedbaccess instead. Users in this role are restricted to using existing logins and cannot create new ones.

Permissions granted to DB_AccessAdmin:

• db_accessadmin sp_addalias
• db_accessadmin sp_dropalias
• db_accessadmin sp_dropuser
• db_accessadmin sp_grantdbaccess
• db_accessadmin sp_revokedbaccess

DB_SecurityAdmin

The DB_SecurityAdmin role offers the ability to manage permissions, ownership, and roles within a database. However, this role can only manage logins that have already been granted access to the database. If you prefer managing permissions using Windows NT groups, you can assign NT groups to SQL roles and then add or remove members from the NT groups.

Permissions granted to DB_SecurityAdmin:

• db_securityadmin DENY
• db_securityadmin GRANT
• db_securityadmin REVOKE
• db_securityadmin sp_addapprole
• db_securityadmin sp_addgroup
• db_securityadmin sp_addrole
• db_securityadmin sp_addrolemember
• db_securityadmin sp_approlepassword
• db_securityadmin sp_changegroup
• db_securityadmin sp_changeobjectowner
• db_securityadmin sp_dropapprole
• db_securityadmin sp_dropgroup
• db_securityadmin sp_droprole
• db_securityadmin sp_droprolemember

DB_BackupOperator

The DB_BackupOperator role allows users to perform backups, DBCC commands, and Checkpoint operations within a database. However, it is important to note that some DBCC commands may not be available to this role. This role is typically useful for scheduled backups, and restores must be done by members of the SysAdmins or DBCreator roles.

Permissions granted to DB_BackupOperator:

• db_backupoperator BACKUP DATABASE
• db_backupoperator BACKUP LOG
• db_backupoperator CHECKPOINT

DB_DDLAdmin

The DB_DDLAdmin role allows its members to issue Data Definition Language (DDL) commands within a database. This role is useful for developers making schema changes or for users who need to have their own objects. However, ownership chains need to be managed if objects are owned by users instead of the DBO. The role can also manage permissions on objects owned by its members.

Permissions granted to DB_DDLAdmin:

• db_ddladmin All DDL but GRANT, REVOKE, DENY
• db_ddladmin dbcc cleantable
• db_ddladmin dbcc show_statistics
• db_ddladmin dbcc showcontig
• db_ddladmin REFERENCES permission on any table
• db_ddladmin sp_changeobjectowner
• db_ddladmin sp_fulltext_column
• db_ddladmin sp_fulltext_table
• db_ddladmin sp_recompile
• db_ddladmin sp_rename
• db_ddladmin sp_tableoption
• db_ddladmin TRUNCATE TABLE

DB_Owner

The DB_Owner role has all permissions within a database. Objects created by members of this role will belong to the user and not to the DBO, unless the user is also a member of the SysAdmins role. If ownership chains need to be avoided, the sp_changeobjectowner stored procedure can be used to change the ownership of objects to DBO.

Permissions granted to DB_Owner:

• Has all permissions in the database

Fixed roles in SQL Server provide a convenient way to manage permissions and access within a database. However, it is important to understand the limitations and potential pitfalls associated with each role. Use them wisely and according to your specific requirements.

If you have any questions or comments, feel free to email me. Your feedback is valuable to me.

If you found this article helpful, please take a moment to rate it and let me know how I’m doing.