70-457 Reviewer #14 Database and Application Roles

This lesson is part two of lesson Reviewer #13 Managing SQL Server Logins. There is so much to cover about securing SQL that i decided to cover the entire lesson in parts. From the first lesson we covered mainly logins and types of logins and fixed server roles. The assumption in this part that you are already familiar with SQL logins and Windows Authenticated logins.

Database Roles

Database roles enables you to assign permission to database users on a collective basis. Instead of assigning permissions to single logins every time you need to create one, you assign the permission to the database role, and add users to this database roles.

Fixed database roles

There are 9 fixed database roles in SQL Server. These roles cannot be altered at any time and by no one.

  • db_owner – members are able to perform database configuration and management
  • db_securityadmin – members are able to manage security of the database
  • db_accessadmin – members are able to manage database access for logins
  • db_backupoperator – members are able to do backup tasks
  • db_ddladmin – members are able to run data definition language commands
  • db_datawriter – members are able to write data in user tables
  • db_datareader – members are able to read data in user tables
  • db_denydatawriter – members are blocked from writing data to user tables
  • db_denydatareader – members are blocked from reading data from user tables

You can use sp_addrolemember from SQL Server 2008R2, but moving forward with SQL Server 2012 you need to change this to ALTER ROLE command. The sp_addrolemember will be phased out in the next release of SQL Server.

ALTER ROLE [db_owner] ADD MEMBER [Domain\User]

Flexible Database-Level Roles

Flexible database-level roles enable you to create with custom database-level roles or permissions. You can use them to apply permissions to a group of logins. To create a role, you will need the CREATE ROLE permission on the database or must be a member of the db_securityadmin database role.

For example with need a database role that can create tables within the database. For this we run command

CREATE ROLE TableCreator AUTHORIZATION ‘domain\usercreator’;

Then to grant permission to this TableCreator role and allow him to create tables within the database, we run;


Now that we have this TableCreator role that is granted permission to create tables within the database, we start adding members to this role by;

ALTER ROLE TableCreator ADD MEMBER [domain\user];

msdb Roles

The msdb system database has additional fixed database roles. These roles enable you to assign permissions associated with Integration Services, Data Collector, Server Groups, database mirroring and PBM or Policy Based Management.

  • The db_ssisadmin, db_ssisoperator, db_ssisltduser msdb database roles enable you to assign permissions to principals for Integration Services.
  • The dc_admin, dc_operator, dc_proxy msdb database roles used in granting permissions to principals for Data Collector.
  • The ServerGroupAdministratorRole and ServerGroupReaderRole msdb database roles grants permission to principal for Server Groups.
  • The dbm_monitor msdb role is used when working with database mirroring. You will not be able to see this role until the database is registered for mirroring.
  • The PolicyAdministratorRole enables you to assign permissions to principals for administering the PBM policies and conditions.
  • The SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUser enables you to assign permissions to principals for the SQL Server Agent

Application Roles

Application roles enable you to grant permissions to a specific application. Let’s say you have a web application that uses a SQL Server database. Rather than have the user access the database with a user permission, the user accesses the data by using permissions assigned to the application role. During the time a user needs access to the data, the application executes the sp_setapprole stored procedure, authenticating the user. When authenticated, the application role is enabled and the connection uses the permissions assigned to the application role.

To create an application role, we use the CREATE APPLICATION ROLE statement as below


Alternatively, you can do this in your SSMS, go to Security menu, go to Roles, and right click on the Application Roles and select New Application Role.

Least Privilege

The principle of least privilege is a principle of information security that is a must practice for database administrators. The principle behind this is that DBA should only grant the least privilege required for a login to perform a task.

For example, rather than giving outright db_owner permission to database role that needs to create tables, you need to create a user defined database role TableCreator, as in our example above. Then grant this role permission to create tables.

Correctly applying the principle of least privilege requires a thorough understanding of the tasks the must be performed by each security principal and creating roles made for each task, and only those tasks to be performed.

To be effective in applying this principle in your environment, it is helpful that you create a user matrix table, listing out all the principals and the tasks that they need to perform. This would be a great place to start when planning.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.


Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment

%d bloggers like this: