You maintain a SQL Server 2008 instance.
You plan to deploy an instance of SQL Server Reporting Services (SSRS) to the same
machine.
You need to ensure that by default. SSRS maintains version histories of all deployed reports.
What should you do?

You administer a SQL Server 2008 instance.
You need to configure the instance to use a single thread for queries that have an estimated
execution cost less than 3.
Which sp_configure configuration option should you set?

You are managing two SQL Server 2008 computers called SQLTEST1 and SQTEST2,
which include a copy of a database named Sales. The database is replicated between SQL1
and SQL2 by using transactional replication. A full backup of each database is performed
every night.
Transaction log backups are performed every hour. Replication latency is typically less than
two minutes. One afternoon, the Sales database on SQLTEST1 becomes corrupted. You
are unable to repair the database. The Sales database on SQLTEST2 is unaffected. You
need to return the Sales database on SQLTEST1 to normal operation as quickly as possible.
You must ensure a minimum loss of data and minimal impact to users of either server.
What should you do?

You administer a SQL Server 2008 instance for a company named Contoso Ltd. The
instance contains a database named DB1.
A Windows group named CONTOSO\Managers can access the DB1 database.
CONTOSO\Managers is a member of the db_owner role in the DB1 database.
A Windows user named User1 is a member of the CONTOSO\Managers group.
You need to ensure that User1 is unable to access the SQL Server instance.
Which Transact-SQL statement(s) should you execute in the DB1 database?

You administer a SQL Server 2008 instance that contains a database named DB1. DB1
contains a table named Table1.
The DB1 database includes a stored procedure named Procedure1. Procedure 1 uses a
sp_executesql Transact-SQL statement to select data from Table1.
According to business requirements, users are not allowed to access tables directly in any
database.
When a user executes Procedure1, the following exception is raised:
“Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object Table1′, database ‘DB1’, schema ‘dbo’.”
You need to ensure that the user can successfully execute Procedure1 without violating the
business requirements.
What should you do?

You administer a SQL Server 2008 instance. A database developer named User1 views the
definitions of all database objects in a database to read data from all user-defined tables,
views, and table-valued functions. You need to set the required permissions for User1. You
also need to ensure that the same permissions can be granted to other developers by
executing minimum number of Transact-SQL statements. Which Transact-SQL statements
should you execute?

You administer a SQL Server 2008 instance.
The security policy permits members of a Windows group named CONTOSO\Sales to
establish new connections to the SQL Server instance only during business horns between
07:00 horns and
19:00 hours. Other users may connect to the SQL Server instance any time.
You write the following Transact-SQL statements.

You need to implement the company security policy that is stored in the
RestrictedLogonHours table.
What should you do?

You administer a SQL Server 2008 instance named CorpPub that contains a database
named SalesSupport The SalesSupport database contains the Products table.
You plan to create a Replication topology to replicate the Products table to the SQL Server
instances installed in portable computers. When the portable computers reconnect to the
corporate network, the Products table is updated from CorpPub.
The Products table on the CorpPub instance is frequently updated between reconnections.
You need to successfully implement the Replication topology along with the Subscription
type by ensuring that bandwidth usage is minimized.
What should you do?

You are mastering the company database; you should use the SQL Server 2005 database
which enables business users to operate both ad hoc and predefined searches. Youdoubt
that some searched will cost too many resources. You should clarify which searches cost the
most resources. You decide to finish this aim as quickly as possible. Which is the correct
answer?

You administer a SQL Server 2008 instance that contains a database named Sales.
A SQL Server login named SalesAppLogin maps to a database user named SalesAppUser
in the Sales database.
You need to ensure that the database user can perform only the following tasks:

-Execute all stored procedures that currently exist in the Sales database. -Execute all stored
procedures that will be created in the Sales database.
What should you do?