Home » Microsoft » MB6-886 » Which additional action(s) should you take?
You are a database administrator on an instance of SQL Server 2008. You have implemented database roles and used role membership to grant all user permissions.
Your company has recently hired a contractor who needs permissions associated with the Developer role. The Developer role has permission to query all tables, as well as to create database objects. Due to privacy concerns, you do not want the contractor to be able to access specific table columns in the Employee table, which the Developer role allows.
You create the Contractor user. You want to assign the appropriate permissions with the least administrative effort.
Which additional action(s) should you take?
A. Create a new database role with only the allowed contractor permissions and make the Contractor user a member of the new role.
B. Create an application role for the Contractor user.
C. Make the Contractor a member of the Developer role and explicitly deny permissions at the column level.
D. Explicitly grant the allowed contractor permissions to the Contractor user.
Correct Answer: C
Explanation/Reference:
EXEC sp_addrolemember N’Developer ‘, N’Contractor’
GO
DENY SELECT, UPDATE ON Employee (HireDate, Salary) TO Contractor;
GO
You should not create a new database role with only the allowed contractor permissions and make the Contractor user a member of the new role. In this scenario, you should grant membership to the role, but override permissions at the user level. Creating a separate role would require more effort than granting membership to the role, but overriding permissions at the user level. If you had a team of contractors that all needed similar access or the desired permissions were significantly different than those provided with the Developer role, then you might create a new role to use for all contractors.
You should not create an application role for the Contractor user. Application roles are not granted specifically to users. Applications roles are activated by an application and are used to restrict application users to only the actions allowed for the application.
You should not explicitly grant the allowed contractor permissions to the Contractor user. This would require much more effort because all permissions granted to the Developer role would have to be explicitly granted.