70-765 試験問題を無料オンラインアクセス

A new Azure Active Directory security principal named [email protected] should have access to select all current and future objects in the Reporting database. You should not grant the principal any other permissions. You should use your Active Directory Domain Services (AD DS) account to authenticate to the Azure SQL database.
You need to create the new security principal.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

正解：

Explanation:
Step 1:
To provision an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database (here the Reporting database) with an Azure AD identity (not with a SQL Server account) that has access to the database.
Step 2: CREATE USER ... FROM EXTERNAL PROVIDER
To create an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity, as a user with at least the ALTER ANY USER permission. Then use the following Transact-SQL syntax:
CREATE USER <Azure_AD_principal_name>
FROM EXTERNAL PROVIDER;
Step 3:
Grant the proper reading permissions.
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication

You develop a Microsoft SQL Server 2014 database that contains a heap named OrdersHistorical.
You write the following Transact-SQL query:
INSERT INTO OrdersHistorical
SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement.
Which table hint should you use?

• A.XLOCK
• B.UPDLOCK
• C.ROWLOCK
• D.TABLOCK
• E.HOLDLOCK

You have a database named DB1 in a Microsoft Azure virtual machine (VM). You install a certificate named TDECert on the server.
You must encrypt all data at rest and provide real-time encryption and decryption for transmitted and received data.
You need to implement Transparent Data Encryption for DB1.
How should you complete the Transact-SQL statements? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

正解：

Explanation:
To use TDE, follow these steps.
- Create a master key
- Create or obtain a certificate protected by the master key
- Create a database encryption key and protect it by the certificate
- Set the database to use encryption
Example:
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
GO
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My Certificate';
GO
USE AdventureWorks2012;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-2017

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are migrating an on-premises Microsoft SQL Server instance to SQL Server on a Microsoft Azure virtual machine. The instance has 30 databased that consume a total of 2 TB of disk space.
The instance sustains more than 30,000 transactions per second.
You need to provision storage for the virtual machine. The storage must be able to support the same load as the on-premises deployment.
Solution: You create one storage account that has one container. You create multiple VHDs in the container.
Does this meet the goal?

• A.Yes
• B.No

You administer a SQL Server 2014 server that contains a database named SalesDB. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that UserA is disallowed to select from any of the tables in the Customers schema.
Which Transact-SQL statement should you use?

• A.REVOKE SELECT ON Schema::Customers FROM Sales
• B.REVOKE SELECT ON Schema::Customers FROM UserA
• C.EXEC sp_addrolemember 'Sales', 'UserA'
• D.DENY SELECT ON Schema::Customers FROM Sales
• E.DENY SELECT ON Schema::Customers FROM UserA
• F.EXEC sp_droprolemember 'Sales', 'UserA'
• G.DENY SELECT ON Object::Regions FROM UserA
• H.REVOKE SELECT ON Object::Regions FROM Sales
• I.DENY SELECT ON Object::Regions FROM Sales
• J.REVOKE SELECT ON Object::Regions FROM UserA