Microsoft SQL Server Interview Questions And Answers (SET -7)

Microsoft SQL Server Interview Questions And Answers (SET -7)



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.

In replication, what is each set of the source data that is replicated from the source server called?
Article
Each set of source data that is replicated is called an article.

In SSIS, what is the difference between output columns and external columns?
External columns represent the meta data of external data sources and output columns are used be data flow source adapters.
Output columns are used by all data-flow source adapters and transformations. They are not used by destination adapters. Put simply, they represent the metadata of the data flowing out of the component. External columns represent the metadata of external data sources and destinations. As such, only source & destination adapters have external columns because these are the only components whose buffered pipeline data interacts with these external sources and destinations.

You want to be sure your database server if properly secured. You have two instances, a default instance and a named instance installed. Which ports do you need to open?
1433 and a specific port after configuring the named instance to use that specific port
Named instances must be configured to use a specific port and that port then opened in the firewall.

You have a relatively new SQL Server 2005 and msdb is corrupt. How can you fix this database without a restore? (data loss is acceptable)
Stop the server and restart it with a trace flag to allow system databases to be detached. Then copy a new version from the /INSTALL folder.
By starting SQL Server with trace flag 3608 you can detach the msdb database. You can copy a new initial version from the installation CD.

In SQL Server 2005 Integration Services, if you want to import a flat file very quickly that contains only integer data, what type of parsing should you use?
Fast Parse
The fast parse mode that is set on columns for a flat file source connection can import a limited set of data types extremely quickly.

In a new default SQL Server 2005 installation, what is the status of the dedicated administrator connection?
Enabled for local connections only.
The dedicated adminstrator connection is enabled for local connections only by default.

In the REPEATABLE READ isolation level, what phenomena is still possible?
Phantom rows
The answer is Phantoms rows. A phantom row refers to the situation where you execute a DML statement that retrieves data, and another process may add new rows the result set. For example:
SET ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT * FROM table
We return the following rows:
ColumnName
-----------
row1
row2

You are guaranteed to get back at least these rows, and no other user may delete these rows (the rows are locked with a shared lock) This is what is referred to as a repeatable read. However, a user might add another row:
SELECT * FROM table
ColumnName
-----------
row1
row2
row3

To prevent phantoms, use the SERIALIZABLE isolation level.

You are designing a new server that will hold 2 instances of SQL Server 2005 in a consolidation project. One of the existing servers has 4GB of RAM and the other has 2GB. You do not want either instance to feel memory pressure, so you specify a new server with 8GB of RAM. What else should you configure on these instances?
Min and Max memory for each instance
If you are designing multiple instances, you will want to specify the amount of RAM that each has access to. If you do not do this, performance can vary dramatically as the servers compete for memory.

You have specified that a SQL Server 2005 login must respect the password policy on a Windows XP host. Which of the following are valid passwords for the user Kendall?
All of the above are valid
On a Windows XP host, the password policy is not enforced because the appropriate API call is not available on the operating system.

A certificate is which type of security mechanism?
Asymmetric Key
A certificate is an asymmetric key that provides encryption in addition to authentication of an entity.

Which of the following services is instance-aware?
Analysis Services
Of these, only Analysis services is instance-aware.