techpreparation-homepage

Home  Interview Questions  Certifications  Aptitude Questions  Tutorials  Placement Papers  Search  Resume  Soft Skills  Video  Forum  Blog

Android app on Google Play

Technical Interview Questions
Oracle Interview Questions
MySql Interview Questions
MsSql Interview Questions
JDBC Interview Questions
                              .........More

Soft Skills
Communication Skills
Leadership Skills
                              .........More

 

 

  

Microsoft SQL Server Interview Questions and Answers



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 utility test?
Named Pipes
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 replicated database?
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 this?
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 it?
EXTERNAL_ACCESS
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 permission.

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 language?
(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?
Push/Pull
The two types of subscriptions are push and pull.

What is the scope of the control permission in SQL Server 2005?
It allows the grantee the equivalent of ownership in that they have all permissions and can grant them to others.
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?
makepipe
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       2        3       4       5       6       7       8        9       10         11       12       13       14       15       16

Have a Question ? post your questions here. It will be answered as soon as possible.

Check Job Interview Questions for more Interview Questions with Answers