MS Exam 70-764

Question 1

What type of key algorithm does

ENCRYPTBYKEY() use to encrypt data?

Answer 1

Symmetric

Question 2

Name the T-SQL function that encrypts data when using Column-Level Encryption?

Answer 2

ENCRYPTBYKEY()

Question 3

What does sys.column_master_keys return?

Answer 3

Returns a row for each database master key added by using the CREATE MASTER KEY statement. Each row represents a single column master key (CMK).

Question 4

T-SQL for opening a symmetric key named Exam70764 by a password ‘Pw’?

Answer 4

OPEN SYMMETRIC KEY Exam70764

DECRYPTION BY PASSWORD = ‘Pw’;

Question 5

T-SQL for creating a symmetric key named Exam70764 with AES_256 and a password ‘Pwd’ ?

Answer 5

CREATE SYMMETRIC KEY CreditCardCert

WITH ALGORITHM = AES_256

ENCRYPTION BY PASSWORD = ‘Pwd’;

Question 6

What is a Securable?

Answer 6

Any object or resouce in SQL Server that can have an action performed aginst it.

Question 7

What are Permissions?

Answer 7

Are actions that can be performed aginst Securables

Question 8

What are Principals?

Answer 8

Objects performing

Question 9

Does Dynamic Data Masking (DDM) persist the data to disk with the mask applied?

Answer 9

No.

The mask is applied on outputs and data in the database is not changed.

Question 10

What permission must a user have granted to see unmasked data?

Answer 10

The Unmask permission granted under the Database securables.

Question 11

Dynamic Data Masking:
Adding, replacing, or removing the mask of a column requires what permission?

Answer 11

ALTER ANY MASK

Question 12

Dynamic Data Masking

Ture or False: Using SELECT INTO or INSERT INTO to copy data from a masked column into another table will result in masked data in the target table?

Answer 12

True

Question 13

Name the four Dynamic Data Masking functions.

Answer 13

1. default()
2. random()
3. partial()
4. email()

Question 14

T-SQL for adding a Dynamic Data Mask to a show only the last four digits of the column CardNumber in the already existing table dbo.CreditCards

Answer 14

ALTER TABLE dbo.CreditCards

ALTER COLUMN CardNumber ADD MASK WITH (FUNCTION = ‘partial(0, “XX..”, 4)’);

Question 15

Which Dynamic Data Masking function will output a different mask depending on the data type of a column?

Answer 15

default()

Question 16

Name the two special roles for SQL Database and SQL Data Warehouse and where those roles exist.

Answer 16

These database roles exist only in the virtual master database under Security.Roles

1. dbmanager
2. loginmanager

Question 17

T-SQL for creating and enabling a SQL Server Audit named DatabaseSpecToEventlog targeting the Application Log.

Answer 17

USE [master];

GO

CREATE SERVER AUDIT DatabaseSpecToEventlog
TO APPLICATION_LOG;

GO

ALTER SERVER AUDIT DatabaseSpecToEventlog

WITH (STATE = ON);

GO

Question 18

T-SQL for creating and enabling a Database Audit Specification named DatabaseOpsAudit mapped to the server audit DatabaseSpecToEventLog with

DATABASE_CHANGE_GROUP

DATABASE_OBJECT_CHANGE_GROUP

AUDIT_CHANGE_GROUP

Answer 18

CREATE DATABASE AUDIT SPECIFICATION DatabaseOpsAudit

FOR SERVER AUDIT DatabaseSpecToEventLog

ADD (DATABASE_CHANGE_GROUP)

, ADD (DATABASE_OBJECT_CHANGE_GROUP)

, ADD (AUDIT_CHANGE_GROUP)

GO

Question 19

True/False:

You can have multiple specifications linked to a single audit.

Answer 19

FALSE

Question 20

Under the hood, what mechanisum does SQL Server Audits emply to capture events?

Answer 20

Extended Events

Question 21

What three things will cause a new Server Audit file to be created?

Answer 21

1. Anytime the db Engine Starts
2. Disable/Endable Audit
3. File size is maxed