What type of key algorithm does
ENCRYPTBYKEY() use to encrypt data?
Name the T-SQL function that encrypts data when using Column-Level Encryption?
What does sys.column_master_keys return?
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).
T-SQL for opening a symmetric key named Exam70764 by a password 'Pw'?
OPEN SYMMETRIC KEY Exam70764
DECRYPTION BY PASSWORD = 'Pw';
T-SQL for creating a symmetric key named Exam70764 with AES_256 and a password 'Pwd' ?
CREATE SYMMETRIC KEY CreditCardCert
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'Pwd';
What is a Securable?
Any object or resouce in SQL Server that can have an action performed aginst it.
What are Permissions?
Are actions that can be performed aginst Securables
What are Principals?
Does Dynamic Data Masking (DDM) persist the data to disk with the mask applied?
The mask is applied on outputs and data in the database is not changed.
What permission must a user have granted to see unmasked data?
The Unmask permission granted under the Database securables.
Dynamic Data Masking:
Adding, replacing, or removing the mask of a column requires what permission?
ALTER ANY MASK
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?
Name the four Dynamic Data Masking functions.
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
ALTER TABLE dbo.CreditCards
ALTER COLUMN CardNumber ADD MASK WITH (FUNCTION = 'partial(0, "XX..", 4)');
Which Dynamic Data Masking function will output a different mask depending on the data type of a column?
Name the two special roles for SQL Database and SQL Data Warehouse and where those roles exist.
These database roles exist only in the virtual master database under Security.Roles
T-SQL for creating and enabling a SQL Server Audit named DatabaseSpecToEventlog targeting the Application Log.
CREATE SERVER AUDIT DatabaseSpecToEventlog
ALTER SERVER AUDIT DatabaseSpecToEventlog
WITH (STATE = ON);
T-SQL for creating and enabling a Database Audit Specification named DatabaseOpsAudit mapped to the server audit DatabaseSpecToEventLog with
CREATE DATABASE AUDIT SPECIFICATION DatabaseOpsAudit
FOR SERVER AUDIT DatabaseSpecToEventLog
, ADD (DATABASE_OBJECT_CHANGE_GROUP)
, ADD (AUDIT_CHANGE_GROUP)
You can have multiple specifications linked to a single audit.
Under the hood, what mechanisum does SQL Server Audits emply to capture events?
What three things will cause a new Server Audit file to be created?