You are a database administrator in your company. You maintain the Corpdb database on the Sql1 SQL Server 2008 computer.
The database contains data that is specific to the corporate department of the company and isaccessed only by corporate department members.
Due to an organizational restructuring, the corporatedepartment has been relocated.
You are required to transfer the Corpdb database to a SQL Server 2008 instance named Sql2 installed at the new location.
You successfully transfer the database to Sql2 by using SSIS packages.
The managers of the corporatedepartment complain they cannot log in to Sql2 .
Although you can log in, you receive a Select permissiondenied error after executing Transact-SQL queries on the database tables.
You want to ensure that managers inthe corporate department are able to successfully access the Corpdb database and configure security for theusers in the corporate department. What should you do?
A.
Grant the db_owner fixed database role to the corporate department managers on the Corpdb database.
B.
Grant the db_accessadmin fixed database role to the corporate department managers on the Corpdb database.
C.
Grant the db_ securityadmin fixed database role to the corporate department managers on the Corpdb database.
D.
Grant the db_backupoperator fixed database role to the corporate department managers on the Corpdb database.
Explanation:
You should grant the db_owner fixed database role to the managers in the corporate department on the Corpdb database. To enable the managers in the corporate department to access the Corpdb database, you shouldgrant the required read/write permissions to the managers. These permissions can be granted by assigning themthe db_owner fixed database role. The
db_owner fixed database role grants the managers the permission toread, write, or modify any object in the database. If the users are assigned this role, they will be able to access allthe objects in the Corpdb database. You should not grant the db_accessadmin fixed database role to the managers in the corporate department onthe Corpdb database. The db_accessadmin fixed database role will not enable you to access the databasetables. The db_accessadmin fixed database role enables users to enable or disable logins and groups. Theselogins and groups can be Windows authenticated or SQL Server authenticated. You should not grant the db_ securityadmin fixed database role to the managers in the corporate department onthe Corpdb database. The db_ securityadmin fixed database role will not enable users to access the databasetables. The db_ securityadmin fixed database role enables users to manage permissions and modify rolememberships. You should not grant the db_backupoperator fixed database role to the managers in the corporate departmenton the Corpdb
database. The db_backupoperator fixed database role will not enable users to access thedatabase tables. The db_backupoperator fixed database role enables users to back up the database.Objective:
Managing SQL Server SecuritySub-Objective:
Manage logins and server roles.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Security and Protection > Identity andAccess Control > Database-Level Roles