Always Encrypted<\/span><\/p>\nAnswer: 2) Row-Level Security<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Row-Level Security (RLS) controls access to individual rows based on the characteristics of the user executing a query. It uses security predicates to determine which rows a user can view or modify. This is useful when multiple users share the same database but should only access records belonging to their department, region, or assigned organization. RLS operates at the database engine level, so applications do not necessarily need separate filtering logic. Dynamic Data Masking and encryption protect sensitive values differently, but they do not provide row-based authorization. Therefore, Row-Level Security is the appropriate feature for restricting access to particular rows.<\/span><\/p>\nQuestion 162. Which SQL Server feature hides sensitive column values from unauthorized users without changing the underlying data?<\/b><\/h3>\n
1)<\/b> Row-Level Security<\/span>
\n<\/span>2)<\/b> Dynamic Data Masking<\/span>
\n<\/span>3)<\/b> Query Store<\/span>
\n<\/span>4)<\/b> Database Backup<\/span><\/p>\nAnswer: 2) Dynamic Data Masking<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Dynamic Data Masking (DDM) helps prevent unauthorized users from viewing complete sensitive values while keeping the original data unchanged in the database. It can mask information such as phone numbers, email addresses, or credit card values when users query protected columns. The masking is applied when data is returned to the requester rather than permanently modifying stored values. Appropriate permissions can allow authorized users to see the unmasked information. Unlike encryption, DDM is primarily intended to limit exposure of sensitive information during normal query access. Therefore, Dynamic Data Masking is the correct feature for hiding sensitive values from unauthorized database users.<\/span><\/p>\nQuestion 163. Which SQL Server technology helps protect sensitive data by encrypting values so that the database engine cannot directly view the plaintext?<\/b><\/h3>\n
1)<\/b> Query Store<\/span>
\n<\/span>2)<\/b> Always Encrypted<\/span>
\n<\/span>3)<\/b> Row-Level Security<\/span>
\n<\/span>4)<\/b> SQL Server Agent<\/span><\/p>\nAnswer: 2) Always Encrypted<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Always Encrypted is designed to protect sensitive information by encrypting selected column values on the client side. The encryption keys are not directly available to the database engine, which means sensitive plaintext values remain protected even if someone gains elevated database access. Applications that are configured to work with Always Encrypted can decrypt the data when authorized operations are performed. This technology is particularly useful for protecting highly sensitive information such as identification numbers or financial data. It differs from Transparent Data Encryption, which protects database files at rest but does not prevent the database engine from seeing decrypted values during normal processing.<\/span><\/p>\nQuestion 164. Which database object stores a predefined set of SQL statements that can be executed by calling its name?<\/b><\/h3>\n
1)<\/b> View<\/span>
\n<\/span>2)<\/b> Stored procedure<\/span>
\n<\/span>3)<\/b> Index<\/span>
\n<\/span>4)<\/b> Constraint<\/span><\/p>\nAnswer: 2) Stored procedure<\/b><\/p>\n
Explanation:<\/b><\/p>\n
A stored procedure is a programmable database object that contains one or more SQL statements and can accept parameters. Database administrators and developers commonly use stored procedures to encapsulate business logic, perform repetitive operations, and control access to underlying data. Instead of sending multiple individual statements from an application, the application can call the procedure and provide required parameter values. Stored procedures can also simplify maintenance because logic is stored centrally in the database. A view primarily represents query results, while an index improves data retrieval performance. Therefore, a stored procedure is the database object designed to store and execute a predefined collection of SQL statements.<\/span><\/p>\nQuestion 165. Which SQL Server feature automatically calculates a column value based on an expression involving other columns?<\/b><\/h3>\n
1)<\/b> Computed column<\/span>
\n<\/span>2)<\/b> Foreign key<\/span>
\n<\/span>3)<\/b> Trigger<\/span>
\n<\/span>4)<\/b> Synonym<\/span><\/p>\nAnswer: 1) Computed column<\/b><\/p>\n
Explanation:<\/b><\/p>\n
A computed column derives its value from an expression based on other columns in the same table. For example, a table containing quantity and unit price could define a computed column that calculates the total amount automatically. This avoids requiring applications to calculate the value separately each time the record is created or updated. Depending on its definition, a computed column may also be persisted so that SQL Server stores the calculated result. A foreign key establishes a relationship between tables, while a trigger executes automatically when specified database events occur. Therefore, a computed column is the appropriate feature for automatically deriving values from other columns.<\/span><\/p>\nQuestion 166. Which SQL Server statement creates a server-level login?<\/b><\/h3>\n
1)<\/b> CREATE USER<\/span>
\n<\/span>2)<\/b> CREATE LOGIN<\/span>
\n<\/span>3)<\/b> CREATE ROLE<\/span>
\n<\/span>4)<\/b> CREATE ACCOUNT<\/span><\/p>\nAnswer: 2) CREATE LOGIN<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The <\/span>CREATE LOGIN<\/span> statement creates a security principal at the SQL Server instance level. A login is used to authenticate a connection to the SQL Server instance. After authentication, the login can be mapped to a database user in one or more databases. This separation between server-level login and database-level user allows administrators to manage access at different security scopes. <\/span>CREATE USER<\/span> creates a database-level principal rather than a server login. Roles are used to group permissions but do not replace the login creation process. Therefore, <\/span>CREATE LOGIN<\/span> is the correct statement when an administrator needs to create a server-level SQL Server login.<\/span><\/p>\nQuestion 167. Which statement creates a database user that can be mapped to an existing login?<\/b><\/h3>\n
1)<\/b> CREATE LOGIN<\/span>
\n<\/span>2)<\/b> CREATE USER<\/span>
\n<\/span>3)<\/b> CREATE DATABASE<\/span>
\n<\/span>4)<\/b> CREATE SERVER<\/span><\/p>\nAnswer: 2) CREATE USER<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The <\/span>CREATE USER<\/span> statement creates a database-level security principal. A database user can be mapped to an existing server-level login, allowing that login to access the database according to the permissions assigned to the user. This distinction is important in SQL Server security because logins exist at the instance level, while users exist within individual databases. An administrator may create one login and then create corresponding database users in multiple databases as required. <\/span>CREATE LOGIN<\/span> is used for server authentication, not database authorization. Therefore, <\/span>CREATE USER<\/span> is the appropriate statement for creating a database user associated with an existing login.<\/span><\/p>\nQuestion 168. Which permission is required to allow a user to execute a stored procedure?<\/b><\/h3>\n
1)<\/b> SELECT<\/span>
\n<\/span>2)<\/b> EXECUTE<\/span>
\n<\/span>3)<\/b> DELETE<\/span>
\n<\/span>4)<\/b> REFERENCES<\/span><\/p>\nAnswer: 2) EXECUTE<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The <\/span>EXECUTE<\/span> permission allows a user or role to run stored procedures and other executable database objects when applicable. For example, an administrator can grant EXECUTE permission on a specific stored procedure without giving the user direct SELECT, INSERT, UPDATE, or DELETE permissions on the underlying tables. This approach can improve security by allowing users to perform approved operations through controlled procedures. The SELECT permission is primarily used for reading data, while DELETE permits removal of rows. Therefore, when the requirement is specifically to allow a user to run a stored procedure, the appropriate permission is EXECUTE.<\/span><\/p>\nQuestion 169. Which SQL Server statement is used to explicitly assign permissions to a user or role?<\/b><\/h3>\n
1)<\/b> GRANT<\/span>
\n<\/span>2)<\/b> DENY<\/span>
\n<\/span>3)<\/b> REVOKE<\/span>
\n<\/span>4)<\/b> DROP<\/span><\/p>\nAnswer: 1) GRANT<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The <\/span>GRANT<\/span> statement explicitly assigns a specified permission to a database principal such as a user or role. For example, an administrator can grant SELECT permission on a table or EXECUTE permission on a stored procedure. Using GRANT provides controlled access without requiring broader permissions than necessary. <\/span>DENY<\/span> explicitly prevents access, while <\/span>REVOKE<\/span> removes a previously granted or denied permission according to the applicable permission hierarchy. The DROP statement is used to remove database objects and is unrelated to permission assignment. Therefore, GRANT is the correct statement when an administrator needs to provide a user or role with a specific database permission.<\/span><\/p>\nQuestion 170. Which SQL Server statement removes a previously granted permission?<\/b><\/h3>\n
1)<\/b> GRANT<\/span>
\n<\/span>2)<\/b> DENY<\/span>
\n<\/span>3)<\/b> REVOKE<\/span>
\n<\/span>4)<\/b> REMOVE<\/span><\/p>\nAnswer: 3) REVOKE<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The <\/span>REVOKE<\/span> statement is used to remove a previously granted permission or remove an explicit DENY in appropriate permission scenarios. For example, if a user previously received SELECT permission on a table and that access is no longer required, an administrator can use REVOKE to remove the explicit grant. REVOKE is different from DENY because DENY explicitly blocks permission, while REVOKE removes an explicit permission assignment. Administrators should understand SQL Server’s permission hierarchy when multiple roles and permissions are involved. Therefore, REVOKE is the appropriate statement when an administrator needs to withdraw an explicitly assigned permission.<\/span><\/p>\nQuestion 171. Which SQL Server statement explicitly prevents a user or role from receiving a specified permission?<\/b><\/h3>\n
1)<\/b> GRANT<\/span>
\n<\/span>2)<\/b> DENY<\/span>
\n<\/span>3)<\/b> REVOKE<\/span>
\n<\/span>4)<\/b> CANCEL<\/span><\/p>\nAnswer: 2) DENY<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The <\/span>DENY<\/span> statement explicitly prevents a principal from receiving a specified permission through the normal permission hierarchy. For example, an administrator can deny SELECT permission on a table to a user. DENY is different from REVOKE because REVOKE removes an explicit permission assignment, whereas DENY establishes an explicit restriction. SQL Server permission evaluation can involve memberships in multiple roles, so administrators must carefully consider the effective permissions of a principal before applying DENY. GRANT provides access rather than restricting it. Therefore, DENY is the correct statement when the requirement is to explicitly block a specific permission.<\/span><\/p>\nQuestion 172. Which SQL Server constraint ensures that a column value satisfies a specified logical condition?<\/b><\/h3>\n
1)<\/b> CHECK<\/span>
\n<\/span>2)<\/b> FOREIGN KEY<\/span>
\n<\/span>3)<\/b> DEFAULT<\/span>
\n<\/span>4)<\/b> UNIQUE<\/span><\/p>\nAnswer: 1) CHECK<\/b><\/p>\n
Explanation:<\/b><\/p>\n
A CHECK constraint ensures that values inserted or updated in a column satisfy a defined logical condition. For example, a table could use a CHECK constraint to ensure that an employee’s salary is greater than zero or that an age value falls within an acceptable range. SQL Server evaluates the condition when data is inserted or modified. If the condition is not satisfied, the operation is rejected. A FOREIGN KEY enforces relationships between tables, while a UNIQUE constraint prevents duplicate values. Therefore, CHECK is the appropriate constraint when database administrators need to enforce a specific rule on acceptable column values.<\/span><\/p>\nQuestion 173. Which constraint uniquely identifies every row in a SQL Server table?<\/b><\/h3>\n
1)<\/b> CHECK constraint<\/span>
\n<\/span>2)<\/b> DEFAULT constraint<\/span>
\n<\/span>3)<\/b> PRIMARY KEY constraint<\/span>
\n<\/span>4)<\/b> FOREIGN KEY constraint<\/span><\/p>\nAnswer: 3) PRIMARY KEY constraint<\/b><\/p>\n
Explanation:<\/b><\/p>\n
A PRIMARY KEY constraint uniquely identifies each row within a table. The column or combination of columns used as the primary key must contain unique values and cannot contain NULL values. Primary keys are commonly used as the main identifier for records and can also be referenced by foreign keys in related tables. A CHECK constraint validates values against a condition, while a FOREIGN KEY establishes referential relationships between tables. A DEFAULT constraint supplies a value when one is not explicitly provided. Therefore, the PRIMARY KEY constraint is the correct choice when the objective is to uniquely identify every row in a table.<\/span><\/p>\nQuestion 174. Which SQL Server constraint prevents duplicate values in a column while allowing the column to remain separate from the primary key?<\/b><\/h3>\n
1)<\/b> FOREIGN KEY<\/span>
\n<\/span>2)<\/b> UNIQUE<\/span>
\n<\/span>3)<\/b> CHECK<\/span>
\n<\/span>4)<\/b> DEFAULT<\/span><\/p>\nAnswer: 2) UNIQUE<\/b><\/p>\n
Explanation:<\/b><\/p>\n
A UNIQUE constraint ensures that duplicate values are not stored in the constrained column or combination of columns. It is useful when a table has an attribute that must remain unique but is not selected as the primary key. For example, an organization may require every employee’s email address to be unique while using an employee ID as the primary key. A table can have multiple UNIQUE constraints, whereas it normally has only one primary key constraint. FOREIGN KEY establishes relationships, CHECK validates conditions, and DEFAULT supplies values. Therefore, UNIQUE is the correct constraint for preventing duplicate values in a non-primary-key column.<\/span><\/p>\nQuestion 175. Which type of SQL Server backup contains a complete copy of the database at the time the backup is created?<\/b><\/h3>\n
1)<\/b> Differential backup<\/span>
\n<\/span>2)<\/b> Transaction log backup<\/span>
\n<\/span>3)<\/b> Full backup<\/span>
\n<\/span>4)<\/b> Partial backup<\/span><\/p>\nAnswer: 3) Full backup<\/b><\/p>\n
Explanation:<\/b><\/p>\n
A full database backup contains a complete backup of the database data required for restoration. It provides a baseline from which additional backup strategies can be built. Organizations often combine full backups with differential and transaction log backups to meet recovery requirements while controlling backup storage and duration. A differential backup contains changes made since the most recent full backup, while a transaction log backup records transaction log activity for supported recovery models. Full backups are therefore fundamental to many SQL Server recovery strategies. When the requirement is to create a complete backup of the database at a particular point, a full backup is the appropriate choice.<\/span><\/p>\nQuestion 176. Which SQL Server backup contains changes made since the most recent full database backup?<\/b><\/h3>\n
1)<\/b> Full backup<\/span>
\n<\/span>2)<\/b> Differential backup<\/span>
\n<\/span>3)<\/b> Transaction log backup<\/span>
\n<\/span>4)<\/b> Copy-only log backup<\/span><\/p>\nAnswer: 2) Differential backup<\/b><\/p>\n
Explanation:<\/b><\/p>\n
A differential backup contains data that has changed since the most recent full database backup. As additional changes occur, a differential backup may become larger because it continues to include changes made after that baseline full backup. During restoration, the most recent full backup and the appropriate differential backup are generally used as part of the recovery sequence. This differs from a transaction log backup, which captures transaction log records and supports point-in-time recovery when the recovery model and backup strategy allow it. Therefore, when the requirement is to back up changes since the latest full backup, a differential backup is the correct option.<\/span><\/p>\nQuestion 177. Which backup type records transaction log information and can support point-in-time recovery?<\/b><\/h3>\n
1)<\/b> Full backup<\/span>