Best Microsoft MCTS Certification, Microsoft MCITP Training at certkingdom.com
QUESTION 4
You work as a database administrator at Certkingdom.com. Certkingdom.com is using a SQL Server 2008
instance. The SQL Server 2008 instance hosts quite a few applications. ABC also has a server
named ABC-SR43 that is used to store log files.
You have to set up a new job that stores log files on ABC-SR43. You create a new account
ABC\Log_Account. You set it up to be run by the SQL Server Agent Services.
However, your job fails to store any log files on ABC-SR43. After some investigation you notice
that the job does not have permissions to store files on ABC-SR43.
How should you remedy the situation? Select the best option.
A. You should set up ABC\Log_Accoun as a Remote Service account.
B. You should set up ABC\Log_Accoun as a Domain service account.
C. You should set up ABC\Log_Accoun as a Local Service account.
D. You should set up ABC\Log_Accoun as a Local System account.
E. You should set up ABC\Log_Accoun as a Network Service account.
F. You should set up ABC\Log_Account as a Domain account.
Answer: F
Explanation:
The service startup account defines the Microsoft Windows account in which SQL Server Agent
runs and its network permissions. SQL Server Agent runs as a specified user account. For
compatibility with earlier versions of SQL Server, SQL Server Agent can also run as the Local
System account.
In this scenario you should select a Domain account, which allows sufficient permissions and
improved security.
QUESTION 5
You work as a database administrator at Certkingdom.com. Certkingdom.com is using a SQL Server 2008
instance that has a table named ABC_Training. ABC_ Training contains a column named
ABC_Distance. A new Certkingdom.com training policy states that the training (ie. The ABC_Distance
column in the ABC_Training table) cannot be increased or decreased by more than 3%.
How should you implement the ABC Training Policy?
A. You should consider developing a view which rolls back non-compliant Certkingdom.com policy
changes to ABC_Distance.
B. You should consider developing a stored procedure which rolls back non-compliant Certkingdom.com
policy compliant changes to the ABC_Distance column.
C. You should consider developing a primary key constraint to the ABC_training table which only
contains valid values on ABC_Distance.
D. You should consider developing a create trigger which rolls back non-compliant Certkingdom.com
policy changes to the ABC_Distance column.
E. You should consider developing an update trigger which rolls back non-compliant Certkingdom.com
policy changes to the ABC_Distance column.
F. You should consider developing a delete trigger which rolls back non-compliant Certkingdom.com policy
changes to the ABC_Distance column.
Answer: E
Explanation:
QUESTION 6
You work as a database administrator at Certkingdom.com. Certkingdom.com is using a SQL Server 2008
instance that contains a database named ABC_DB. Certkingdom.com contains a lot of customer data
which is processed by the Web application. You need to keep the customer information safe since
it is confidential. This information includes files of data, backups, and log files.
How can this be achieved and still keep the performance and functionality of the web application?
Select one or two.
A. You should consider adding a transaction Log on ABC_DB, and set the information to be
encrypted for a fixed time.
B. Use BitLocker Drive Encryption on the hard drives on the SQL Server.
C. Use EFS (Encrypting File System) on the hard drives on the SQL Server.
D. You should consider enabling the Transparent Database Encryption on ABC_DB and back up
the transaction log.
E. You should consider enabling the Transparent Database Encryption on ABC_DB and enabling
the Transparent Database Encryption on master database.
F. Use cell-level encryption for the specific data that needs to be kept safe.
Answer: D
Explanation:
With the introduction of transparent data encryption (TDE) in SQL Server 2008, users now have
the choice between cell-level encryption as in SQL Server 2005, full database-level encryption by
using TDE, or the file-level encryption options provided by Windows. TDE is the optimal choice for
bulk encryption to meet regulatory compliance or corporate data security standards. TDE works at
the file level, which is similar to two Windows® features: the Encrypting File System (EFS) and
BitLocker™ Drive Encryption, the new volume-level encryption introduced in Windows Vista®,
both of which also encrypt data on the hard drive. TDE does not replace cell-level encryption,
EFS, or BitLocker.
QUESTION 7
You work as a database administrator at Certkingdom.com. Certkingdom.com is using two SQL Server 2008
sample named ABCSmp1 and ABCSmp2. Furthermore, ABCSmp1 contains a database named
ABC_DB. A Certkingdom.com user named Mia Hamm uses her logon MiaHamm to log on to the database
on ABCSmp1. During the week you have transferred ABC_DB to ABCSmp2. You manually
recreate the Mia Hamm login on ABCSmp2. However, when she tries to login on ABC_DB at
ABCSmp2, she received an error message stating that she is not allowed access. Mia Hamm
needs to access ABC_DB and you need to make it happen.
What T-SQL code should you use? (Each correct option is part of the answer. Choose TWO)
A. Use Certkingdom.com;
B. Use ABC_DB; *
C. Use ABCSmp1;
D. Use ABCSmp2;
E. ALTER LOGIN MiaHamm WITH DEFAULT_DATABASE = ABC_DB;
F. CHANGE LOGIN MiaHamm WITH DEFAULT_DATABASE = ABC_DB;
G. ALTER LOGIN MiaHamm WITH PASSWORD = ‘pwd1234’ UNLOCK;
H. CHANGE LOGIN MiaHamm WITH PASSWORD = ‘pwd1234’ UNLOCK;
I. CHANGE USER MiaHamm WITH LOGIN = MiaHamm;
J. ALTER USER MiaHamm WITH LOGIN = MiaHamm; *
K. ALTER LOGIN MiaHamm ENABLE;
L. CHANGE LOGIN MiaHamm ENABLE;
Answer: B,J
Explanation:
QUESTION 8
You work as a database administrator at Certkingdom.com. Certkingdom.com is using a SQL Server 2008 instance.
Your junior assistant is curious about on how to move a table, such as ABC_table, from its current
scheme (scheme1) to another scheme (scheme2)
How can you accomplish this in T-SQL?
A. You should consider using the following:
ALTER TABLE Schema1.ABC_Table SWITCH TO Schema2.ABC_table;
B. You should consider using the following:
ALTER AUTHORIZATION ON Schema1.ABC_Table TO Schema2;
C. You should consider using the following:
ALTER SCHEMA schema2 TRANSFER schema1.ABC_table;
D. You should consider using the following:
ALTER SCHEMA schema1 TRANSFER schema2.ABC_table;
E. You should consider using the following:
ALTER USER Schema1 WITH DEFAULT_SCHEMA = Schema2;
F. You should consider using the following:
MOVE ABC_Table FROM SCHEMA schema1 TO schema2
G. You should consider using the following:
MOVE ABC_Table FROM schema1 TO schema2
Answer: C
Explanation:
ALTER SCHEMA can only be used to move securables between schemas in the same database.
To change or drop a securable within a schema, use the ALTER or DROP statement specific to
that securable.
QUESTION 9
You work as a database administrator at Certkingdom.com. Certkingdom.com is using a SQL Server 2008
instance. You have received instructions from management to allow the users at Certkingdom.com on the
SQL Server sample to use the OPENROWSET() function to search remote information sources.
You study incomplete code in the exhibit carefully. (Line numbers are used for reference purposes only)
Exhibit:
01. sp_configure ‘show advanced options’, 1
02. RECONFIGURE
03.
04. RECONFIGURE
05. GO
06.
07. SELECT a.*
08. FROM OPENROWSET(‘SQLNCLI’, ‘Server=Madrid;Trusted_Connection=yes;’,
09. ‘SELECT GroupName, Name, DepartmentID
10. FROM AdventureWorks.HumanResources.Department
11. ORDER BY GroupName, Name’) AS a;
12. GO
Which T-SQL statement should you insert at line 03 to make the batch meet the requirement in
this scenario?
A. sp_configure ‘Router control’, 1
B. sp_configure ‘Router control’, 0
C. sp_configure ‘Transaction Logs’, 1
D. sp_configure ‘Transaction Logs’, 0
E. sp_configure ‘Ad Lib Distributed Queries ‘, 1
F. sp_configure ‘Ad Lib Distributed Queries ‘, 0
G. sp_configure ‘Ad Hoc Distributed Queries’, 1
H. sp_configure ‘Ad Hoc Distributed Queries’, 0
Answer: G
Explanation:
By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and
OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. When this
option is not set or is set to 0, SQL Server does not allow ad hoc access.
Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to
connect to remote data sources that use OLE DB. OPENROWSET and OPENDATASOURCE
should be used only to reference OLE DB data sources that are accessed infrequently. For any
data sources that will be accessed more than several times, define a linked server.
Best Microsoft MCTS Certification, Microsoft MCITP Training at certkingdom.com
