To recover an instance of Notification Services in
2005 on another server, what information would you need?
A backup of the database holding Notification Services
and the XSD and XSLT files as well as the name and
password for the service account.
To recover SSNS, you need the database backup as well as
the operational files, XSD and XSLT files, stored on the
file system and the account information used for the
service account. Since SSNS usually works with data
outside of the SQL Server, a domain account is usually
used to ensure proper permissions.
What type of connectivity does the readpipe/makepipe
The readpipe and makepipe utility combination will test
named pipe connectivity.
In SQL Server 2005, which of the following schema
changes are supported for the publication objects of a
All of the above.
SQL Server 2005 replicated databases support the
following schema changes for objects:
* ALTER TABLE
* ALTER VIEW
* ALTER PROCEDURE
* ALTER FUNCTION
* ALTER TRIGGER (DML only)
You want to disable the receipt of messages in one of
your databases by the Service Broken. How should you do
ALTER DATABASE Sales SET DISABLE_BROKER
To disable the receipt of messages, you can disable the
Service Broker by disabling it with the ALTER DATABASE
command. The SET command is "DISABLE_BROKER".
You are building a .NET assembly that will access the
registry of the local machine for a factor used in a
computer column. What permission set should you assign
The most restrictive permission set should always be
used for .NET assemblies in keeping with a secure SQL
Server environment. Only the EXTERNAL_ACCESS and UNSAFE
permission sets will allow registry access and UNSAFE
permissions are not required.
You are developing security policy for your SQL Servers
and have all of the data entry clerks needing access to
a series of tables. You create a Data Entry role and
assign the proper permissions as well as add the users.
You then find out that Bob is a part of the HR group
because of cross training and needs to use the same
objects from the same application except for the
Vacation table, to which he should not have access. No
column permissions are assigned. What should you do?
Create an new role and DENY permission to the Vacation
table for this role. Add Bob to this role.
To effectively handle security, you want to minimize the
administrative burden. Bob is a member of the Data Entry
role, and because of cross training, you do not want to
remove him from this role, but you do need to DENY
permission to the Vacation table. The best way to do
this is with another role specifically to DENY this
You are trying to track down issues with a SQL Server
2005 application using Profiler. The part of the
application you are checking uses the EXECUTE AS
statement to change the context of every user to the
user Bob. How can you determine which user is executing
statements in Profiler?
Check the Sessionloginname data column.
The SESSIONLOGINNAME column, not shown by default, will
contain the original login name of the user even if
their context has changed in the application.
Assuming the column name is correct, which of the
following is a valid cast operation in SSIS _expression
(DT_STR, 20, 1252)[MyColumn]
The correct answer is (DT_STR, 20, 1252)[MyColumn]. The
cast uses parens and then the cast type specification
with the _expression to be converted afterwards.
There are two types of subscriptions in SQL Server
replication. What are they?
The two types of subscriptions are push and pull.
What is the scope of the control permission in SQL
It allows the grantee the equivalent of ownership in
that they have all permissions and can grant them to
The Control permission is equivalent to assigning
ownership of the securables. All available permissions
are granted to the principal, and they the principal in
turn can grant those permissions to others.
When testing connectivity in SQL Server, you use which
utility in conjunction with the readpipe utility to
establish a connection?
The makepipe utility is used with the readpipe utility
to test connectivity only for named pipes connections.
This utility has been deprecated in SQL Server 2005.
Page Numbers : 1