DP-300 Question #374: Real Exam Question with Answer & Explanation

Question

SIMULATION You need to provide a user named user2-12345678 with the ability to back up and restore databases and change the compute setting of the databases located on a server named sql12345678. User2-12345678 must be prevented from connecting to the databases and modifying database server settings. The solution must minimize administrative effort. 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 assign roles to logins and database users in SQL Server by using SQL Server Management Studio or Transact-SQL. Use roles in SQL Server to efficiently manage permissions. Assign permissions to roles, and then add and remove users and logins to the roles. By using roles, permissions do not have to be individually maintained for each user. To add a member to a user-defined database role Step 1: In Object Explorer, expand the server [Here sql12345678] in which you want to edit a user-defined database role. Expand the Security folder. Step 2: Expand the Databases folder. Step 3: Expand the database in which you want to edit a user-defined database role. [Select all the databases] Step 4: Expand the Security folder. Step 5: Expand the Roles folder. Step 6: Expand the Database Roles folder. Step 7: Right-click the role you want to edit and select Properties. Step 8: In the Database Role Properties -database_role_name dialog box, in the General page, click Add. Step 9: In the Select Database User or Role dialog box, under Enter the object names to select (examples), enter the login or database role to add to this database role [Select db_backupoperator]. Alternately, click Browse... and select any or all of the available objects in the Browse for Objects dialog box. Click OK to return to the Database Role Properties - database_role_name dialog box. Note: Fixed-database roles The following table shows the fixed-database roles and their capabilities. These roles exist in all databases. * db_backupoperator Members of the db_backupoperator fixed database role can back up the database. * Etc. Step 10: Select OK. Reference: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/join-a- role https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication- access/database-level-roles