--- PDF PAGE 356 ---
341
Listing 10-7.  Adding and Removing Server Roles
--Add Danielle to the sysadmin Role
ALTER SERVER ROLE sysadmin ADD MEMBER Danielle ;
GO
--Remove Danielle from the sysadmin role
ALTER SERVER ROLE sysadmin DROP MEMBER Danielle ;
GO
Implementing Database-Level Security
We have seen how security at the instance level is managed using logins and server 
roles. Security at the level of the individual database has a similar model, consisting of 
database users and database roles. The following sections describe this functionality.
Database Roles
Just as there are server roles at the instance level that help manage permissions, there 
are also database roles at the database level that can group principals together to assign 
common permissions. There are built-in database roles, but it is also possible to define 
your own, ones that meet the requirements of your specific data-tier application.
The built-in database roles that are available in SQL Server 2019 are described in 
Table 10-2.
Table 10-2.  Database Roles
Database Role
Description
db_accessadmin
Members of this role can add and remove database users from the 
database.
db_backupoperator
The db_backupoperator role gives users the permissions they need 
to back up the database, natively. It may not work for third-party backup 
tools, such as CommVault or Backup Exec, since these tools often 
require sysadmin rights.
(continued)
Chapter 10  SQL Server Security Model

--- PDF PAGE 357 ---
342
Database Role
Description
db_datareader
Members of the db_datareader role can run SELECT statements 
against any table in the database. It is possible to override this for 
specific tables by explicitly denying a user permissions to those tables. 
DENY overrides the GRANT.
db_datawriter
Members of the db_datawriter role can perform DML (Data 
Manipulation Language) statements against any table in the database. 
It is possible to override this for specific tables by specifically denying a 
user permissions against a table. The DENY will override the GRANT.
db_denydatareader
The db_denydatareader role denies the SELECT permission against 
every table in the database.
db_denydatawriter
The db_denydatawriter role denies its members the permissions to 
perform DML statements against every table in the database.
db_ddladmin
Members of this role are given the ability to run CREATE, ALTER, 
and DROP statements against any object in the database. This 
role is rarely used, but I have seen a couple of examples or poorly 
written applications that create database objects on the fly. If you are 
responsible for administering an application such as this, then the  
ddl_admin role may be useful.
db_owner
Members of the db_owner role can perform any action within the 
database that has not been specifically denied.
db_securityadmin
Members of this role can grant, deny, and revoke a user’s permissions 
to securables. They can also add or remove role memberships, with the 
exception of the db_owner role.
Table 10-2.  (continued)
You can create your own database roles in SQL Server Management Studio by 
drilling down through Databases ➤ Your Database ➤ Security and then selecting New 
Database Role from the context menu of database roles in Object Explorer. This displays 
the General tab of the Database Role - New dialog box. Here, you should specify  
db_ReadOnlyUsers as the name of our role and state that the role will be owned by dbo. 
dbo is the system user that members of the sysadmin server role map to. We have then 
used the Add button to add Danielle to the role.
Chapter 10  SQL Server Security Model

--- PDF PAGE 361 ---
346
A good schema design for this example would involve three schemas, which are split 
by business responsibility—Sales, Procurement, and Accounts. Table 10-3 demonstrates 
how these tables can be split and permissions can then be assigned to the tables via 
database roles.
Figure 10-5.  Entity relationship diagram
Chapter 10  SQL Server Security Model

--- PDF PAGE 362 ---
347
The command in Listing 10-9 creates a schema called CH10 and then grants the user 
Danielle SELECT permissions on the dbo schema. This will implicitly give her SELECT 
permissions on all tables within this schema, including new tables, which are yet to be 
created.
Listing 10-9.  Granting Permissions on a Schema
CREATE SCHEMA CH10 ;
GO
GRANT SELECT ON SCHEMA::CH10 TO Danielle ;
To change a table’s schema post creation, use the ALTER SCHEMA TRANSFER 
command, as demonstrated in Listing 10-10. This script creates a table without 
specifying a schema. This means that it is automatically placed in the dbo schema.  
It is then moved to the CH10 schema.
Table 10-3.  Schema Permissions
Schema
Table
Database Role
Permissions
Sales
OrderHeader
OrderDetails
Customers
Addresses
Sales
SELECT, INSERT, UPDATE
Accounts
SELECT
Procurement
Products
Vendors
Purchasing
SELECT, INSERT, UPDATE
Sales
SELECT
Accounts
SELECT
Accounts
Invoices
CustAccountHistory
VendAccountHistory
Accounts
SELECT, INSERT, UPDATE
Chapter 10  SQL Server Security Model