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 other s 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 master database:

CREATE LOGIN BI_User WITH PASSWORD = ‘Pa$$wørd’

You run the following Transact-SQL statement in the business intelligence database:

Does the solution meet the goal?

A. Yes

B. No

Explanation:

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 isnt 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