Question 8
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:
CREATE USER BI_User WITH PASSWORD = 'Pa$$w0rd'
GRANT EXECUTE TO BI_User
EXEC sp_addrolemember 'db_datawriter', 'BI_user'
Does the solution meet the goal?
Correct answer: B
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. https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/managing-permissions-with- stored-procedures-in-sql-server
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.
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/managing-permissions-with- stored-procedures-in-sql-server