What should you do?

You are the SQL administrator for your company. Two SQL Server 2008 computers named SQL_prod and SQL_test are used by your company.
Due to recent security policies established by your company, you need to ensure that a user cannot establishmore than two sessions with SQL_prod.
Users can establish as many sessions as necessary with SQL_test . What should you do?

A.
Create a DML trigger on SQL_prod.

B.
Create a DDL trigger on SQL_prod.

C.
Create a logon trigger on SQL_prod.

D.
Create a stored procedure on SQL_prod.

Explanation:

You should create a logon trigger on SQL_prod . Logon triggers fire in response to a LOGON event. The FORLOGON clause specifies that a trigger is a LOGON trigger. For example, the following script creates a trigger thatfires if there are already two user sessions created by the JohnD user:
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS ‘JohnD’
FOR LOGON AS
BEGIN
IF ORIGINAL_LOGIN()= ‘JohnD’ AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = ‘JohnD’) > 2
ROLLBACK;
END;
You should not create a Data Manipulation Language (DML) trigger on SQL_prod . DML triggers fire when DMLstatements, such as INSERT , UPDATE , and DELETE statements, are issued on tables or views. You should not create a Data Definition Language (DDL) trigger on SQL_prod . DDL triggers fire when DDLstatements such as CREATE , ALTER , DROP , GRANT , DENY , REVOKE , or UPDATE STATISTICS statements,are issued. You should not create a stored procedure on SQL_prod . A stored procedure cannot be configured to executeautomatically in response to a certain event. Triggers are configured to execute automatically in response tocertain events.

Objective:
Managing SQL Server Security

Sub-Objective:
Manage SQL Server instance permissions.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > LogonTriggers TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference >CREATE TRIGGER (Transact-SQL)