You have a number of users in your customer service department who need Select, Insert, and Update permissions, but they should not be able to delete—only managers should have the permission to delete data. How can you ensure that only managers can delete data and users can only perform the tasks listed?

You have a number of users in your customer service department who need Select, Insert, and Update permissions, but they should not be able to delete—only managers should have the permission to delete data. How can you ensure that only managers can delete data and users can only perform the tasks listed?

You have a number of users in your customer service department who need Select, Insert, and Update permissions, but they should not be able to delete—only managers should have the permission to delete data. How can you ensure that only managers can delete data and users can only perform the tasks listed?


Answer: – Add the users to a custom role that allows only Select, Insert, and Update permissions; add the managers to the db_datareader and db_datawriter roles.

(No fixed database role allows the permissions that the users need, but the managers need the permissions that are allowed by the db_datareader and db_datawriter roles. Therefore, you need to use fixed roles for the managers and custom roles for the users.)