You work as the database administrator at Domain.com. Domain.com has its headquarters in New York, and branch offices in Boston, Chicago and Atlanta. Each office currently maintains its own database for customers in a single Microsoft Access table.
Domain.com wants to centralize the Microsoft Access databases in a single SQL Server 2005 database named Certkiller -DB01 that is located in the New York office. You enable the local administrator at each office to import data from the Microsoft Access databases into Certkiller -DB01. You need to ensure that the local administrator at each office has the minimum permissions necessary to perform the operation.
What should you do? (Each correct answer presents part of the solution. Choose TWO.)
A.
Use BULK INSERT to import the data to SQL Server 2005.
B.
Add the local administrators to the db_datawrite role.
C.
Add the local administrators to the db_dtsltduser role.
D.
Add the local administrators to the db_dtsoperator role.
E.
Use SQL Server Integration Service (SSIS) to create a package.
Explanation:
The legacy data must be transformed; SSIS allows you to transform and import data to SQL Server 2005. The local administrator needs permission to run the SSIS package; therefore you need to add them to the db_dtsoperator role.
Incorrect Answers:
A: BULK INSERT does not transform data.
B, C: The db_dtsltduser role and db_datawrite role gives the administrator too much permissions.