70-764 Administering a SQL Database Infrastructure - Practice Questions

Question 13

You install a Microsoft SQL Server 2016 instance.

The instance will store data extracted from two databases running on Windows Azure SQL Database.

You hire a data steward to perform interactive data cleansing and ad hoc querying and updating of the database.

You need to ensure that the data steward is given the correct client tools to perform these tasks.

Which set of tools should you install?

Select an option, then click Submit answer.

○
SQL Server Management Studio and Distributed Replay Client
○
Master Data Services and Data Quality Client
○
Data Quality Client and Distributed Replay Client
○
Data Quality Client and SQL Server Management Studio

Question 14

You have a Microsoft SQL Server database named DB1. Nested and recursive triggers for DB1 are disabled. There is an existing DDL trigger named TableAudit in the database. The trigger captures the name of each table as the table is created.

You define a policy for the database by using SQL Server policy-based management. The policy requires that all table names use the prefix tbl. You set the evaluation mode for the policy to On change: prevent.

You observe that developers are able to add new tables that do not include the required prefix in the table name.

You need to ensure the policy is enforced.

What should you do?

Select an option, then click Submit answer.

○
Enable recursive triggers
○
Change the policy evaluation mode to On Schedule
○
Disable the TableAudit trigger
○
Enable nested triggers

Question 15

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 section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

Your company is developing a new business intelligence application that will access data in a Microsoft Azure SQL Database instance. All objects in the instance have the same owner.

A new security principal named BI_User requires permission to run stored procedures in the database. The stored procedures read from and write to tables in the database. None of the stored procedures perform IDENTIFY_INSERT operations or dynamic SQL commands.

The scope of permissions and authentication of BI_User should be limited to the database. When granting permissions, you should use the principle of least privilege.

You need to create the required security principals and grant the appropriate permissions.

Solution: You run the following Transact-SQL statement in the database:

Does the solution meet the goal?

Select an option, then click Submit answer.

○
Yes
○
No

Reference / correct answer:

No

We need to add a login.

Secondly, it is enough to grant EXECUTE permissions on the stored procedures for database roles you want to be able to access the data. We do not need to add roles to this user.

Note: One method of creating multiple lines of defense around your database is to implement all data access using stored procedures or user-defined functions. You revoke or deny all permissions to underlying objects, such as tables, and grant EXECUTE permissions on stored procedures. This effectively creates a security perimeter around your data and database objects.

Best Practices

Simply writing stored procedures isn't enough to adequately secure your application. You should also consider the following potential security holes.

Grant EXECUTE permissions on the stored procedures for database roles you want to be able to access the data.

Revoke or deny all permissions to the underlying tables for all roles and users in the database, including the public role. All users inherit permissions from public. Therefore denying permissions to public means that only owners and sysadmin members have access; all other users will be unable to inherit permissions from membership in other roles. Do not add users or roles to the sysadmin or db_owner roles. System administrators and database owners can access all database objects.

References: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/managing-permissions-with-stored-procedures-in-sql-server

70-764 – Administering a SQL Database Infrastructure