70-764 SQL Server 2016 Database Administration (DBA)

Security implementation for SQL Server usually begins at the server level, where users are authenticated, based on logins, and organized into server-level roles to make it easier to manage permissions. Lesson Objectives After completing this lesson, you will be able to:

 Describe the concepts important to understanding SQL Server security.

 Describe SQL Server authentication options and work with them.

 Understand how the Azure® SQL database firewall operates and is configured.

 Manage logins and policies in SQL Server

The containment feature in SQL Server reduces the reliance a database has on the SQL Server instance that hosts it. This is very useful for optimizing the moving of a database to another SQL Server instance. This lesson discusses the containment feature, how it is deployed, and the considerations for using partially contained databases. Lesson Objectives After completing this lesson, you will be able to:

 Understand partially contained databases.

 Understand the considerations for using partially contained databases.

 Create a partially contained database.

Roles defined at server level are used to control access to server-level permissions. This lesson covers server-level permissions, the purposes of the built-in server roles, and how to create server-level roles that are customized to your requirements. Lesson Objectives After completing this lesson, you will be able to:  Describe server-scoped permissions.  Explain typical server-scoped permissions.  Give an overview of fixed server roles.  Explain the public server role.  Create user-defined server roles.

In SQL Server, roles are supported at database level. The purpose of database-level roles is similar to the purpose of the server-level roles that you have already learned about in this module—to simplify the administration of database permissions. This lesson covers the fixed database roles available in every SQL Server database. Lesson Objectives After completing this lesson, you will be able to:  Explain database-scoped permissions.  Use fixed database roles.  Assign users to roles.  Describe the purpose of the database owner role

In addition to providing you with control over who accesses data in your database or the objects in your server, SQL Server helps you to control which users can execute your code. Appropriate security control of code execution is an important aspect of your security architecture. In this lesson, you will see how to manage the security of stored procedures and functions. You will also learn how to manage security for code that lives in .NET-managed code assemblies that are used with SQL CLR integration. Finally, you will see how ownership chains affect the security relationship between code and database objects. Lesson Objectives After completing this lesson, you will be able to:  Secure stored procedures.  Secure user-defined functions.  Secure managed code.  Manage ownership chains.