SIMULATION You need to create a new database role named role1 that can read the contents of the SalesLT.SalesOrderDetail table only in db1. To complete this task, sign in to the virtual machine. You…

Submitted by klara.se· Mar 6, 2026Implement database security and manage access control by creating roles and assigning granular table-level permissions in Azure SQL / SQL Server databases.

Question

SIMULATION You need to create a new database role named role1 that can read the contents of the SalesLT.SalesOrderDetail table only in db1. To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal. Answer: You can create a new server role in SQL Server by using SQL Server Management Studio or Transact-SQL. Using SQL Server Management Studio to create a new server role Step 1: In Object Explorer, expand the server where you want to create the new server role. Step 2: Expand the Security folder. Step 3: Right-click the Server Roles folder and select New Server Role.... Step 4: In the New Server Role -server_role_name dialog box, on the General page, enter a name for the new server role in the Server role name box. [Enter role1] Step 5: In the Owner box, enter the name of the server principal that will own the new role. Alternately, click the ellipsis (...) to open the Select Server Login or Role dialog box. Step 6: Under Securables, select one or more server-level securables [Select the SalesLT.SalesOrderDetail table]. When a securable is selected, this server role can be granted or denied permissions on that securable. Step 7: In the Permissions: Explicit box, select the check box to grant, grant with grant, or deny permission to this server role for the selected securables [Select Read]. If a permission cannot be granted or denied to all of the selected securables, the permission is represented as a partial select. Step 8: On the Members page, use the Add button to add logins that represent individuals or groups to the new server role. [Skip] Step 9: A user-defined server role can be a member of another server role. On the Memberships page, select a check box to make the current user-defined server role a member of a selected server role. [Skip] Step 10: Select OK. Reference: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create- a-server-role

Explanation

The correct approach creates a DATABASE role (not a server role) named role1 in db1, then grants SELECT permission specifically on the SalesLT.SalesOrderDetail table. This is achieved by navigating to db1 > Security > Roles > Database Roles in SSMS and creating the role there, or by using T-SQL commands: CREATE ROLE role1 in the context of db1, followed by GRANT SELECT ON SalesLT.SalesOrderDetail TO role1. This satisfies the requirement of scoping read access to only that specific table within db1.

Topics

#Database Roles#SQL Server Permissions#GRANT Statement#SQL Server Management Studio

Community Discussion

No community discussion yet for this question.

Full DP-300 Practice