MCSA-SQL Database Administrator 2016 Training

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 Lecture describes SQL Server security models, logins, users, partially contained databases, and cross-server authorization.

·         Lessons

·         Authenticating Connections to SQL Server

·         Authorizing Logins to Connect to databases

·         Authorization Across Servers

·         Partially Contained Databases

·         Lab : Authenticating Users

·         Create Logins

·         Create Database Users

·         Correct Application Login Issues

·         Configure Security for Restored Databases

·         After completing this Lecture, you will be able to:

·         SQL Server basic concepts.

·         SQL Server connection authentication.

·         User login authorization to databases.

·         Partially contained databases.

·         Authorization across servers.  

Using roles simplifies the management of user permissions. With roles, you can control authenticated users’ access to system resources based on each user’s 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.

·         Lessons

·         Working with server roles

·         Working with Fixed Database Roles

·         Assigning User-Defined Database Roles

·         Lab : Assigning server and database roles

·         Assigning Server Roles

·         Assigning Fixed Database Roles

·         Assigning User-Defined Database Roles

·         Verifying Security

·         After completing this Lecture, you will be able to:

·         Describe and use server roles to manage server-level security.

·         Describe and use fixed database roles.

·         Use custom database roles to manage database-level security.

·         Use application roles to manage database-level security.

In the previous Lectures, 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 Lecture, 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 Lecture.

·         Lessons

·         Authorizing User Access to Objects

·         Authorizing Users to Execute Code

·         Configuring Permissions at the Schema Level

·         Lab : Authorizing users to access resources

·         Granting, Denying, and Revoking Permissions on Objects

·         Granting EXECUTE Permissions on Code

·         Granting Permissions at the Schema Level

·         After completing this Lecture, you will be able to:

·         Authorize user access to objects.

·         Authorize users to execute code.

·         Configure permissions at the schema level.

When configuring security for your Microsoft SQL Server systems, you should ensure that you meet any of your organization’s 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 Lecture describes the available options for auditing in SQL Server, how to use and manage the SQL Server Audit feature, and how to implement encryption.

·         Lessons

·         Options for auditing data access in SQL Server

·         Implementing SQL Server Audit

·         Managing SQL Server Audit

·         Protecting Data with Encryption

·         Lab : Using Auditing and Encryption

·         Working with SQL Server Audit

·         Encrypt a Column as Always Encrypted

·         Encrypt a Database using TDE          

·         After completing this Lecture, you will be able to:

·         Describe the options for auditing data access.

·         Implement SQL Server Audit.

·         Manage SQL Server Audit.

·         Describe & implement methods of encrypting data in SQL Server.

·         Implement encryption