You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2005
instance. You have multiple applications running fine on this server. You receive a new third-party
application that will not run in SQL Server 2005, as it requires SQL Server 2008.
You are required to make sure that all these applications should run without changing the
environment of the applications and with the minimal amount of cost. What action should you
take? (Each correct option is part of the answer. Choose TWO)

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance. You have received instructions to publish an instance of SQL Server Reporting Services
(SSRS) that will keep the old information to the same machine, and as default as possible.
What action should you take?

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance. You have received instruction from management to set the instance to utilize one thread

for searches which owns an estimated execution cost less than 6.
What steps should you take?

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance that contains a database named ABC_Prod. ABC.com also has a Windows group named
ABC\Employees that makes use of ABC_Prod. Some of the users in that group are members for
the db_owner role. However, one member in the ABC\ Employees group named Mia is not
allowed to access the SQL Server instance. You have received instructions from the CEO to make
sure that the Mia does not access the SQL Server instance. You need to complete this task using
the appropriate Transact-SQL statement.
Which would be the best solution?

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance. The SQL Server 2008 instance contains a database named ABC_DB that contains a
table named ABC_Tbl. ABC_DB also contains a stored procedure named ABC_Prod that uses a
sp_executesql Transact-SQL statement to get data from ABC_Tbl.
Everything is going fine. Employees are using ABC_Prod as user ABC_User and it works fine.
Some moths later a new ABC.com security policy is implemented. This security policy does not
allow users to access tables directly in any database. Now the employees are complaining. When
they use ABC_prod they get an error saying: The SELECT permission was denied on the object ‘
ABC_Tbl’, database ‘ ABC_DB ‘, schema ‘dbo’.
What action would be best?

UESTION NO: 36
You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance. You have received instructions from your boss, Miss Tess King, to comply with a new set
of users that will be hired by ABC. The first user is KeithClark. Later a new group of users named
Hirelings will be added as well. They should all be able to:
•view the definitions from all user-defined tables
•read data from all user-defined tables
•read data from all user-defined views
•read data from all user-defined functions
You need to complete this task with the least amount of SQL administrative statements. You start
by created the user KeithClark.
What other steps are required? Select all that apply.

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance that contains a database named ABC_Prod. ABC.com has a SQL Server login named
ABC_Tst _Login that maps to a ABC.com learner administrator named ABC_ProdAppUser in
ABC_Prod. You have received instruction from management that you need to restrict
ABC_ProdAppUser so that it only can run stored procedures (current and future). It should not be
allowed to execute other tasks in the database.
What action should you take?

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
database that makes use of the Analysis Services and Reporting Services. However, installed
components at ABC.com adhere to the ABC.com security policy. You have received instruction to
ensure that you adhere to the ABC.com security policy.
What steps should you take? (Each correct option is part of the answer. Choose THREE)

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance with failover clustering. You have received instructions from management to encrypt the
connections in the failover cluster.
How can this be accomplished? Select all that apply.

You work as a database administrator at ABC.com. ABC.com has 12 separate branch offices and
a main office, each office is equipped with their own SQL Server 2008 computer.
A database table named ABC-Sales is used to store point-of-sale transactions. Furthermore, ABCSales also hosts sales information from all 12 of the ABC.com branch offices to accommodate
customer returns of products. Consequently the data should be refreshed from the main office to
all 12 branch offices every hour, on the hour. To this end you make use of a trigger called
Stats_Trigger to produce sales statistics based on customer sales and buying patterns. Currently
replication between the server in each of the 12 branch offices and a middle server in the main
office occurs in real time. You need to decide which type of replication would be best in this
scenario. You should also eliminate the replication of the data that is produced by the trigger.
What steps should you take? (Each correct answer presents part of the solution. Choose TWO.)