In this article, we will see about fixed database roles in SQL Server. As the name suggests, fixed database roles cannot be removed or modified and performs specific administrative tasks as it has pre-defined set of permissions. You need to be very careful while assigning these roles and assign these roles only when there is a serious requirements. You can execute the sp_helpdbfixedrole system procedure to get the list of fixed database roles.
EXEC sp_helpdbfixedrole
GO
- db_owner
- db_accessadmin
- db_securityadmin
- db_ddladmin
- db_backupoperator
- db_datareader
- db_datawriter
- db_denydatareader
- db_denydatawriter
You can navigate to Database Roles from Object Explorer –> Expand Databases Node –> Select Database –> Navigate to Security –> Roles –> Database Roles
db_owner
All the members of db_owner fixed database roles can perform all the maintenance activities and setting configurations. This role should not be given to regular users and should be assigned very carefully as it can perform almost all the operations in a database.
db_accessadmin
As the name suggests, all the members of db_accessadmin can handle access related issues. This role controls security, grants access, revokes access for logins to enter database. This role is rarely used as these operations are performed by DBAs as he has relevant fixed server role.
db_securityadmin
Members of db_securityadmin role manages all the permissions and security related activities. As database administrators usually manages security, permissions, role membership etc. so this role is hardly assigned and used. You should not assign this role to regular users.
db_ddladmin
If any member is assigned with db_ddladmin role then he can perform all DDL operations and can execute, create, drop and alter any objects. Normally this role is assigned to developers to perform the related operations in application. This role is usually not assigned to regular users as he can misuse the DDL operations.
db_backupoperator
db_backupoperator role can perform the database backup operations. This role is rarely used as backup activity is a role of database administrator and he has much higher permissions rather than using this specific fixed database role.
db_datareader
If any user is requesting for SELECT permission on database tables then you can tag him with db_datareader fixed database role as this role allows a member to perform SELECT operations on database tables and views and tagged member will not be able to modify any object. This role is mostly assigned to developers and regular users who need table access on production database.
db_datawriter
All the members of db_datawriter fixed database role can perform INSERT, UPDATE, DELETE operations on all tables and views in respective database. This role is basically assigned to developers to perform operations on QA servers. Testing applications sometimes require this role.
db_denydatareader
As the name suggests this role doesn’t allow to read data from tables in a database. So user will not be able to perform SELECT operations on a table or views. I haven’t used this role any time and also never seen members assigned with this role.
db_denydatawriter
This role is basically opposing db_datawriter role which means members with this role will not be able to perform INSERT, UPDATE or DELETE operations on tables and views. Again as a DBA I have not used this role yet and never seen anyone using this role.
HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.