Set - 7

Question 6 :

You have created a new assembly and want to test it on SQL Server 2005. You install the developer edition with defaults on your workstation and create the assembly as a sysadmin. You then write a function to use this assembly, but it does not work. What could be the problem?

Answer :

The CLR environment needs to be enabled.
By default on SQL Server 2005, the CLR runtime environment, which is what executes the .NET assemblies, is not enabled. You need to execute the following code to enable it:

Question 7 :

The Service Broker operates on messages in which fashion?

Answer :

Like a queue, first in, first out.
Messages transferred in Service Broker work in a queue fashion with the first message being sent being the first processed.

Question 8 :

You have a user that agrees to take over some of the database administration for your SQL Server 2005. They will be in charge of granting access to one particular database used by the time card application for server logins. What security role should you assign them?

Answer :

This user will not add logins, but rather grant database level access for existing logins, therefore the minimum role they need is the db_accessadmin fixed database role. This allows them the ability to add access for Windows logins, Windows group logins, or SQL Server logins.

Question 9 :

What does this method in SQL Server 2005's SMO equate to in T-SQL?

Answer :

This SMO method executes a DBCC CHECKDB with the NO_INFOMSGS option set.

Question 10 :

In SQL Server 2005, can you include a user-defined role as a member of another user-defined role?

Answer :

Yes you can.