SQL Server 2008 R2: Security Management

This course is included in our On-demand training solution.


Microsoft SQL Server 2008 R2 provides enhanced security features to ensure that each instance is secure. Using SQL Server 2008 R2 security features, administrators can guarantee that access to a SQL Server instance is granted only to specified users, using either Windows Authentication or Mixed Mode Authentication by creating logins and then mapping the logins to the databases within the instance. Administrators also have the ability to encrypt a SQL Server instance using transparent data encryption (TDE) and to encrypt the data within a database using encryption certificates and asymmetric and symmetric keys. Impersonation is another SQL Server security feature, which is used to make sure that the person who accesses the database has the appropriate permissions to complete the task, for example, running a specific stored procedure to access underlying objects. This course demonstrates how to create SQL Server logins, database users, and how to grant permissions, including server and database roles. It also addresses how to use encryption to protect a database and database data, how to use impersonation using the EXECUTE AS clause, and how to configure auditing to monitor access to SQL Server. This course is one of a series in the SkillSoft learning path that covers the objectives for Microsoft SQL Server 2008 exam number 70-432. Passing this exam will earn the learner the Microsoft Certified Technology Specialist (MCTS): Microsoft SQL Server 2008, Implementation and Maintenance certification.

Target Audience

Professionals typically pursuing a career as a Database Administrator; individuals who don’t work directly with SQL Server as part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators.


One to two years of experience using SQL Server 2008, SQL Server 2008 R2 or two to three years of experience using a previous version of SQL Server

Expected Duration

120 min.

Course Objectives

SQL Server 2008 R2 Resource Security

  • recognize how security works in the SQL Server 2008 R2 Database Engine
  • SQL Server 2008 R2 Login, Schema, and User Creation

  • recognize how to create a login for SQL Server 2008 R2
  • recognize how to create a schema for SQL Server 2008 R2
  • recognize how to use T-SQL to create a user for SQL Server 2008 R2
  • Data Encryption in SQL Server 2008 R2

  • recognize how to implement encryption in SQL Server 2008 R2
  • Impersonation in SQL Server 2008 R2

  • recognize how to use impersonation in a given scenario
  • Audit Access to SQL Server 2008 R2

  • recognize how to create a DDL trigger to audit access
  • recognize how to create a logon trigger to audit access
  • recognize how to create an event notification to audit access
  • Auditing Logins in SQL Server 2008 R2