SQL Server 2008 exam-2

Which of the following is done during the compilation process that verifies that all object and column names in the query are correct? a. parsing b. optimization c. normalization d. compilation

c. normalization

What do you call any type of authenticated user logged into the SQL Server? a. sa b. stored procedure c. principal d. securable

c. principal

Which of the following stored procedures add a new job executed by the SQLServerAgent service? a. sp_dpoption b. sp_configure c. sp_add_job d. sp_who

c. sp_add_job

Which commands prevents the principal from having the permission specified on the securable? a. REVOKE b. DENY c. EXCHANGE d. GRANT e. ACCESS

a. REVOKE

Which program would you use to change the authentication mode? a. SQL Server Management Studio b. SQL Server Surface Area Configuration c. SQL Server Books Online d. SQL Server Configuration Manager

a. SQL Server Management Studio

What performs operations that combine multiple values into one value by grouping, summarizing, or averaging the values? a. aggregate functions b. built-in functions c. CLR functions d. scalar functions

a. aggregate functions

When you are performing forensics, what do you call whoever gathers and preserves the evidence must also maintain a complete inventory that shows who has handled specific items and where they have been stored? a. chain of custody b. evidence collection c. evidence preservation d. jurisdiction

a. chain of custody

Which type of column would be a based on a deterministic function? a. computed column b. scalar column c. mass calculated column d. CLR column

a. computed column

What can set up an agreement when you send and receive information using the Service Broker, you need to establish a _________. a. contract b. messenger c. router d. conversation

a. contract

What are the application-defined messages used to communicate between services? a. conversation b. messenger c. query d. router

a. conversation

You are setting up a kiosk in a library that hundreds of people will access every month. You want to make sure visitors to the library have access to read data from the SQL Server, but they should not be able to change any of the data. You need to accomplish this with the least administrative overhead possible. What should you do? a. Enable the guest user account in Windows. No guest login or database accounts need to be created in SQL Server because they already exist. Add the guest account to the db_denydatawriter and db_datareader roles. b. Create a Windows account named Kiosk, and map a SQL login to that account. Then create a database user account for Kiosk, and add it to the db_denydatawriter and db_datareader roles. Finally, have all the library patrons log in to the computer system as Kiosk. c. Enable the guest account in Windows, and map a SQL login to it. Then create a guest database user account, and add it to the db_denydatawriter and db_datareader roles. d. Enable the guest user account in Windows, and map it to a SQL login. No database user account named guest will need to be created because it already exists in each database. Add the guest account to the db_denydatawriter and db_datareader roles.

d. Enable the guest user account in Windows, and map it to a SQL login. No database user account named guest will need to be created because it already exists in each database. Add the guest account to the db_denydatawriter and db_datareader roles.

When a user logs into a SQL server using their Windows account, they are using __________ for authentication. a. Basic authentication b. AppleTalk c. SQL authentication d. Kerberos

d. Kerberos

Which permission set allows you to change the ownership of an object? a. Control b. Update c. Alter d. Take Ownership

d. Take Ownership

Which fixed database role allows members to back up the database? a. db_ddladmin b. db_accessadmin c. db_datawriter d. db_backupoperator

d. db_backupoperator

What is a piece of code or routine that accepts parameters and stored as an object in the SQL server? a. CLR b. catalog c. function d. index

c. function

What is a constraint defined once and applied to potentially many tables? a. foreign key constraint b. rule c. unique constraint d. default constraint

d. default constraint

What policy defines how people and resources will be protected in the case of a disaster and how recovery will be performed? a. chain of custody b. business continuity plan c. backup plan d. disaster recovery plan

d. disaster recovery plan

What key links another table by matching the primary key in the other table? a. matched key b. indexed key c. primed key d. foreign key

d. foreign key

What returns a table and is referenced in the FROM clause, just like a view? a. aggregate functions b. scalar functions c. multistatement table-valued functions d. inline table-valued function functions

d. inline table-valued function functions

The primary key must consist of a column or columns that contain _________ values. a. encrypted b. primed c. constraint d. unique

d. unique

Which command gives the ability to add permission to an object? a. GRANT b. DENY c. REVOKE d. EXCHANGE e. ACCESS

a. GRANT

You have an HR database that all users will be allowed to read from to obtain information, but only the HR department should be able to read from and update the data in the database. What is the easiest and most secure way for you to ensure this? a. Add all the users who are not in the HR department to the db_datareader and db_denydatawriter database roles, and add all the users from the HR department to the db_datareader and db_datawriter database roles. b. Add all the users who are not in the HR department to the db_datareader and db_denydatawriter database roles, and add all the users from the HR department to the db_owner database role. c. Add all the users who are not in the HR department to the db_datareader database role, and add all the users from the HR department to a custom database role that allows them all modification and selection permissions. d. Add all the users who are not in the HR department to the db_datareader and db_denydatawriter database roles, and add all the users from the HR department to the db_datamodifier database role.

a. Add all the users who are not in the HR department to the db_datareader and db_denydatawriter database roles, and add all the users from the HR department to the db_datareader and db_datawriter database roles.

What is the best method of data recovery? a. Backup b. Use RAID c. Use spare drives d. Use a cluster

a. Backup

You are the administrator of a SQL Server system that will be used only for development access; the server will have no production databases on the server whatsoever. All your developers need to be able to create databases and objects inside the databases, such as tables, views, and so on. Which roles should they be added to at the server and database levels to accommodate these needs? a. db_creator at the server level and db_ddladmin at the database level b. sysadmins at the server level and db_owner at the database level c. sysadmins at the server level and db_ddladmins at the database level d. db_creator at the server level and db_owner at the database level

a. db_creator at the server level and db_ddladmin at the database level

SQL server roles and fixed database roles should be assigned only in accordance with the ____________________ needed for a job function. a. least privilege b. full privilege c. most privilege d. DMZ privilege

a. least privilege

In SQL security, what is an entity that can be secured with permissions? a. securable b. security identifier c. permission d. principal

a. securable

You want to be able to use email, replication, and other interserver services with SQL Server. When you install SQL Server, what type of account should you use? a. A domain account with no administrative access b. A domain account with administrative privileges c. A local account d. The local server account

b. A domain account with administrative privileges

You have a number of users in your customer service department who need Select, Insert, and Update permissions, but they should not be able to delete—only managers should have the permission to delete data. How can you ensure that only managers can delete data and users can only perform the tasks listed? a. Add the users to a custom role that allows only Select, Insert, and Update permissions and the managers to a custom role that allows them to read and modify data. b. Add the users to a custom role that allows only Select, Insert, and Update permissions and the managers to the db_datareader and db_datawriter roles. c. Add the users to the db_datareader role and the managers to the db_datawriter role. d. Add the users to the db_datareader and db_datawriter roles, and add the managers to the db_datadeleter role.

b. Add the users to a custom role that allows only Select, Insert, and Update permissions and the managers to the db_datareader and db_datawriter roles.

Which is not used to modify permissions to a database object? a. DENY b. EXCHANGE c. REVOKE d. GRANT

b. EXCHANGE

Which object does the Update permission NOT apply to? a. Table b. Query c. View d. Synonyms

b. Query

Which commands removes a permission to an object? a. ACCESS b. REVOKE c. DENY d. EXCHANGE e. GRANT

b. REVOKE

What should you do with the Windows administrators to ensure a separation of duties and limit the ability of non-DBA administrators to work inside the SQL server? a. Add the local Windows administrator group to the Domain Administrators group. b. Remove the Windows administrators group from the SQL Server sysadmin group. c. Enable auditing on the SQL server. d. Remove the administrators group from the root of all file systems.

b. Remove the Windows administrators group from the SQL Server sysadmin group.

Which permission set allows you to query a object and return the data from a table, view, table-valued function or synonym? a. Execute b. Select c. Control d. View

b. Select

What provides facilities for storing message queues in a SQL Server database? a. conversation maker b. Service Broker c. message broker d. router

b. Service Broker

Which fixed roles would you usually granted to developers so they allow access to all tables? a. db_securityadmin and db_accessadmin b. sysadmin and db_accessadmin c. db_datareader and db_datawriter d. public and sysadmin

c. db_datareader and db_datawriter

Which fixed database role allows members to write data from all user tables in the database? a. db_accessadmin b. db_backupoperator c. db_datawriter d. db_ddladmin

c. db_datawriter

One of your users has created a table (John.table1) and granted Samantha Select permission on the table. Samantha, however, does not need to see all the data in the table so she creates a view (Samantha.view1). Thomas now wants access to Samantha's view, so Samantha grants Thomas Select permission on the view. What happens when Thomas tries to select from the view? a. Thomas can select, but he receives an error message stating that he does not have permission on the underlying table. b. Thomas cannot select from the view because he does not have permission on the underlying table and the ownership chain is broken. c. Thomas can select from the view because he has been granted permissions on the view directly. d. Thomas can select from the view because Samantha granted him permission on the view and she has permission on the underlying table.

b. Thomas cannot select from the view because he does not have permission on the underlying table and the ownership chain is broken.

Which permission set allows you to change the value of data in a table, view or synonym? a. Control b. Update c. Select d. Execute

b. Update

A _______________ is a column that is derived from other columns in the table. a. computed column b. aggregate column c. CLR column d. scalar column

b. aggregate column

What restricts the data acceptable in a field even if the data belongs to the correct data type? a. index b. check constraint c. compliant checker d. partition

b. check constraint

What is the act of converting high-level code to machine language code by the Query Optimizer? a. storing b. compiling c. machineconverting d. converting

b. compiling Response Feedback: EXPLANATION: When SQL Server processes a stored procedure, the Query Optimizer first checks the procedure cache for an already in-memory execution plan. If it finds one, it uses that plan to complete the execution request. Otherwise, it takes the time needed to compile and cache a new execution plan prior to executing the query.

Which fixed database role can perform all configuration or management operations in a database? a. db_backupoperator b. db_owner c. db_accessadmin d. db_securityadmin

b. db_owner

Which fixed database role can modify the permission and roles in the database? a. db_backupoperator b. db_securityadmin c. db_accessadmin d. db_ddladmin

b. db_securityadmin

What type of function always returns the same result, giving a specific input value? a. multistatement table-valued functions b. deterministic function c. inline table-valued function functions d. nondeterministic functions

b. deterministic function

What type of function always returns the same result, giving a specific input value? a. multistatement table-valued functions b. deterministic function c. inline table-valued function functions d. nondeterministic functions

b. deterministic function

Which of the following integrity types include data types, rules, default constraints, triggers, and XML schemas as applied to a column? a. entity integrity b. domain integrity c. workgroup integrity d. referential integrity

b. domain integrity

Which function returns the lowercase of a giving string? a. len() b. lower() c. upper() d. ascii()

b. lower()

Which of the following is not a mathematical function included with the SQL server? a. sqrt() b. min() c. abs() d. cos()

b. min()

Which of the following is done during the compilation process that checks for syntax errors? a. compilation b. parsing c. optimization d. normalization

b. parsing Response Feedback: EXPLANATION: Parsing is when SQL Server checks for syntax errors and prepares it for optimization.

What feature is new in SQL Server 2008 to be used with Service Broker? a. queue b. priority c. messenger d. router

b. priority

Which role is created by the SQL server, assigned to all users and has limited rights to read system views only? a. db_owner b. public c. db_securityadmin d. db_denydatawriter

b. public

Which of the following is not part of the SQL Server security architecture? a. permissions b. rights c. principle d. securable

b. rights

Which stored procedure changes global configuration settings for the current server? a. sp_add_job b. sp_configure c. sp_dpoption d. sp_who

b. sp_configure

Which of the following stored procedures gives information about current users, sessions in an instance of the Microsoft SQL Server Database Engine? a. sp_configure b. sp_who c. sp_dpoption d. sp_add_job

b. sp_who

Which of following does SQL server does not support? a. domain integrity b. workgroup integrity c. entity integrity d. referential integrity

b. workgroup integrity

What command would you use to create a queue on the source SQL server? a. MAKE QUEUE SenderQueue b. CREATE QUEUE ReceiverQueue c. CREATE QUEUE SenderQueue d. MAKE QUEUE ReceiverQueue

c. CREATE QUEUE SenderQueue

Which permission set allows you to create new objects? a. Add b. Insert c. Create d. Update

c. Create

Which permission set allows you to invoke the stored procedures, function, or synonym? a. Reference b. Invoke c. Execute d. Impersonate

c. Execute

Which function will return a character string representing the specified date name of the specified date? a. datediff() b. dateadd() c. datename() d. datepart()

c. datename()