𝘛𝘦𝘤𝘩 𝘚𝘰𝘭𝘶𝘵𝘪𝘰𝘯 𝘏𝘶𝘣

Roles are a vital component for regulating access and permissions when users interact with a database. These roles define a collection of database-level permissions, allowing users to execute particular tasks. While SQL Azure includes predefined database roles like “db_owner” that cannot be changed, you have the flexibility to generate and delete customizable database roles. These roles enable you to organize users into cohesive groups, simplifying the application of permissions.

Expand the Azure SQL DB and navigate to security -> Roles -> Database Roles to get a list of available database roles.

The following table lists the database roles and their description.

db_owner

The users in the db_owner fixed database roles provide the highest permissions in a database. Users have the privilege to create, drop, alter, write, or drop the database.

To add a user in the db_owner role, we can use T-SQL stored procedure sp_addrolemember. The following statements provide db_owner permissions to the tshblog.

EXEC sp_addrolemember 'db_owner', 'tshblog';

db_accessadmin

The fixed database role db_accessadmin provides rights to add or remove, create, and manage database users.

The following script provides db_accessadmin role permissions to the tshblog user.

  EXEC sp_addrolemember 'db_accessadmin', 'tshblog';

db_datareader

The db_datareader role grants rights required to read data from all tables and views in the database. For example, suppose you want developers to read data from the production database. In this case, you can provide him db_datareader role in the respective database.

The following script provides db_ datareader role permissions to the tshblog user.

 EXEC sp_addrolemember 'db_datareader', 'tshblog';

db_datawriter

The db_datareader role grants rights required to write(insert, update) data from all tables and views in the database. For example, you can add your application account to this role to perform data inserts, updates.

The following script provides db_datawriter role permissions to the tshblog user.

  EXEC sp_addrolemember 'db_datawriter', 'tshblog';

db_ddladmin

The db_ddladmin fixed database role grants permissions to create and manage database objects. For example, you can add users in this role to create, alter or drop the objects in the database.

The following script provides db_ddladmin role permissions to the tshblog user.

EXEC sp_addrolemember 'db_ddladmin', 'tshblog';

db_denydatareader

The db_denydatareader role denies access for a user to read data from any table or view in the database using the db_denydatareader role.

The following script provides db_denydatareader role permissions to the tshdemo user.

EXEC sp_addrolemember 'db_denydatareader', 'tshdemo';

db_denydatawriter

The db_denydatawrite database role denies a user to write data into the table or view of the database.

The following script provides db_denydatawriter role permissions to the tshdemo user.

EXEC sp_addrolemember 'db_denydatawriter', 'tshdemo'