Administering a SQL Database Infrastructure (OD20764)

Module 1: SQL Server Security

Protection of data within your Microsoft SQL Server databases is essential and requires a working knowledge of the issues and SQL Server security features. This module describes SQL Server security models, logins, users, partially contained databases, and cross-server authorization.

Module 2: Assigning Server and Database Roles

Using roles simplifies the management of user permissions. With roles, you can control authenticated users access to system resources based on each users job function—rather than assigning permissions user-by-user, you can grant permissions to a role, then make users members of roles. Microsoft SQL Server includes support for security roles defined at server level and at database level.

Module 3: Authorizing Users to Access Resources

In the previous modules, you have seen how Microsoft SQL Server security is organized and how sets of permissions can be assigned at the server and database level by using fixed server roles, user-defined server roles, fixed database roles, and application roles. The final step in authorizing users to access SQL Server resources is the authorization of users and roles to access server and database objects. In this module, you will see how these object permissions are managed. In addition to access permissions on database objects, SQL Server provides the ability to determine which users are allowed to execute code, such as stored procedures and functions. In many cases, these permissions and the permissions on the database objects are best configured at the schema level rather than at the level of the individual object. Schema-based permission grants can simplify your security architecture. You will explore the granting of permissions at the schema level in the final lesson of this module.

Module 4: Protecting Data with Encryption and Auditing

When configuring security for your Microsoft SQL Server systems, you should ensure that you meet any of your organizations compliance requirements for data protection. Organizations often need to adhere to industry-specific compliance policies, which mandate auditing of all data access. To address this requirement, SQL Server provides a range of options for implementing auditing. Another common compliance requirement is the encryption of data to protect against unauthorized access in the event that access to the database files is compromised. SQL Server supports this requirement by providing transparent data encryption (TDE). To reduce the risk of information leakage by users with administrative access to a database, columns containing sensitive data—such as credit card numbers or national identity numbers—can be encrypted using the Always Encrypted feature. This module describes the available options for auditing in SQL Server, how to use and manage the SQL Server Audit feature, and how to implement encryption.

Module 5: Recovery Models and Backup Strategies

One of the most important aspects of a database administrators role is ensuring that organizational data is reliably backed up so that, if a failure occurs, you can recover the data. Even though the computing industry has known about the need for reliable backup strategies for decades—and discussed this at great length—unfortunate stories regarding data loss are still commonplace. A further problem is that, even when the strategies in place work as they were designed, the outcomes still regularly fail to meet an organizations operational requirements. In this module, you will consider how to create a strategy that is aligned with organizational needs, based on the available backup models, and the role of the transaction logs in maintaining database consistency.

Module 6: Backing Up SQL Server Databases

In the previous module, you lear