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

You administer a Microsoft SQL Server instance that contains a database of confidential data.
You need to enable Transparent Data Encryption.
Which four 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

The steps to setup TDE are:
Step 1: Create a master key
We must first create the master key. It must be created in the master database.
Example:
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
go
Step 2: Create or obtain a certificate protected by the master key
Once the master key is created, we will go ahead and create the actual certificate in the master database, not the user database.
Example:
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE AdventureWorks2012;
GO
Step 3: Create a database encryption key and protect it by the certificate Now, we must utilize our USE command to switch to the database, the user database, that we wish to encrypt.
Then we create a connection or association between the certificate that we just created and the actual database.
Example:
USE <DB>
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO
Step 4: Set the database to use encryption
Example:
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption

You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. The company uses several custom applications that store data in databases on the Microsoft SQL Server 2012 servers.
A full backup of all databases is taken every night at midnight.
A differential backup of all databases is taken on the hour every hour starting at 3am until the last backup at
11pm.
A log backup is taken every 15 minutes for databases configured with the Full Recovery Model.
One application named ABCApp1 stores data in a database named ABCApp1DB.
ABCApp1DB is configured with the Simple Recovery Model. ABCApp1DB fails at 3:25am.
You discover that the last differential backup of ABCApp1DB failed.
You need to restore ABCApp1DB from backup as quickly as possible and minimize data loss.
Which of the following steps should you perform to restore ABCApp1DB? (Choose one or more answers).

• A.Restore the latest full backup.
• B.Restore the latest differential backup
• C.Restore each log backup since the last full backup.
• D.Restore each differential backup taken since the last full backup.
• E.Restore the latest log backup

You administer a Microsoft Azure SQL Database database named Inventory that contains a stored procedure named p_AddInventory.
Users need to be able to SELECT from all tables in the database and execute the stored procedure.
You need to grant only the necessary permissions.
What should you do?

• A.Create a new database role. Grant EXECUTE permission on p_AddInventory to the new role. Grant VIEW DEFINITION to the role. Add all users to the role.
• B.Create a new database role. Grant EXECUTE permission on p_AddInventory to the new role. Add all users to the role. Add all users to the db_datareader role.
• C.Add all users to the db_datawriter role. Add all users to the db_datareader role.
• D.Grant EXECUTE permission on p_AddInventory to all users. Grant VIEW DEFINITION to all users.

You administer a Microsoft SQL Server 2012 database named ContosoDB. The database contains a table named Suppliers and a column named IsActive in the Purchases schemA.
You create a new user named ContosoUser in ContosoDB. ContosoUser has no permissions to the Suppliers table.
You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions.
Which Transact-SQL statement should you use?

• A.CREATE PROCEDURE Purchases.PurgeInactiveSuppliersWITH EXECUTE AS USER =
  'dbo'ASDELETE FROM Purchases.Suppliers WHERE IsActive = 0GOGRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser
• B.GRANT DELETE ON Purchases. Suppliers TC ContosoUser
• C.CREATE PROCEDURE Purchases. PurgeInactiveSuppliersASDELETE FROM Purchases.Suppliers WHERE IsActive = 0GOGRANT EXECUTE ON Purchases. PurgeInactiveSuppliers TO ContosoUser
• D.GRANT SELECT ON Purchases.Suppliers TO ContosoUser

You administer a Microsoft Azure SQL Database database named Human_Resources. The database contains 2 tables named Employees and SalaryDetails.
You add two Windows groups as logins for the server:
* CORP\Employees - All company employees
* CORP\HRAdmins - HR administrators only
* HR Administrators are also company employees.
You need to grant users access according to the following requirements:
* CORP\Employees should have SELECT access to the Employees table.
* Only users in CORP\HRAdmins should have SELECT access to the SalaryDetails table.
* Logins are based only on Windows security groups.
What should you do?

• A.Create two database roles: Employees and HRAdmins.Add all company employees to the Employees role.Add HR administrators to the HRAdmins role.Grant SELECT access to all tables except SalaryDetails to the Employees role.Grant SELECT access to the SalaryDetails table to the HRAdmins role.Deny SELECT access to the SalaryDetails table to the Employees role.
• B.Create a database role called Employees.Add all HR administrators to the db_datareader role.Add all company employees to the Employees role.Grant SELECT access to all tables except the SalaryDetails table to the Employees role.Deny SELECT access to the SalaryDetails table to the Employees role.
• C.Create a database role called HRAdmins.Add all company employees except HR administrators to the db_datareader role,Add all HR administrators to the HRAdmins role.Grant SELECT access to the SalaryDetails table to the HRAdmins role.Deny SELECT access to the SalaryDetails table to the db_datareader role.
• D.Create a database role called Employees.Add CORP\Employees to the db_datareader role.Add all company employees except HR administrators to the Employees role.Deny SELECT access to the SalaryDetails table to the Employees role.