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 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;
GRANT CREATE TABLE TO TableCreator;
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];
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 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
CREATE APPLICATION ROLE app_Logger WITH PASSWORD = ‘logger’
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.
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.