How To Create Database Role in SQL Server

When authorizing users in databases, you can use system-defined roles such as db_datareader, db_datawriter, db_owner, or database-level permissions, such as Execute, View Definition.

If there are multiple users that we want to grant same privileges, we can create a database role and grant permission to users through this database role.

In order to create a database role, you need CREATE ROLE permission, db_securityadmin or db_owner privilege.

Create a Database Role via SSMS

First, in the database under the Securtiy tab, right-click on the Roles and click New Database Role.

After that, let’s determine the name of the role and the owner of this role. I’m choosing dbo as this owner. If we select a different user as the owner of the role, you can not delete this user without changing the owner of the role.

Right click on the database to grant the required permissions to the role. From the Properties option, open the Permissions tab. Click the search button to select the role we want to authorize.

Write the name of the role and click Checknames and OK.

Then, grant the necessary privileges to the role in the following screen and click ok.

Then, let’s authorize a login on this role in the database.

Right click on the login that you want to authorize on this role and click properties,  then go to the User Mapping tab.

Finally, select database first and then choose MyDatabaseRole.