Problem in creating LOGON trigger

Question

Problem in creating LOGON trigger

jai verma

Old Hand

Points: 302
More actions
July 6, 2009 at 7:17 am

#211823

I have a SQL server version
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
When i try to create a Logon trigger-
CREATE TRIGGER [LogAudit]
ON ALL SERVER FOR LOGON
AS
BEGIN
--action to perform on logon
END
it's givin a message- Msg 1084, Level 15, State 1, Procedure LogAudit, Line 5 'LOGON' is an invalid event type.
Please tell me the solution.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

Are you trying to create a DDL trigger for Logging in of a user? This is not a DDL event. You can enable Login audit by right clicking ur server in SSMS, click on security page and select Both failed and Successful logins

DDL_LOGIN_EVENTS, DDL_GDR_SERVER_EVENTS, DDL_AUTHORIZATION_SERVER_EVENTS, DROP_SERVER_ROLE_MEMBER, ADD_SERVER_ROLE_MEMBER, ADD_ROLE_MEMBER, DROP_ROLE_MEMBER are DDL events for DDL Server Level events.

Refer BOL : ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/wmi9/html/4229ce23-2a9f-4706-92fe-554307988ced.htm

Pradeep Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You're running SQL 2005 RTM. The logon trigger was added in SQL 2005 SP2. You need to install at least SP2 (recommended SP3) to be able to create login triggers.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jai verma Old Hand Points: 302 More actions · Answer 3

What is the difference in SQL 2005 RTM and SQL 2005.

And SP2 is already installed in My SQL server.

Please suggest.

Jai Verma

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

jai verma (7/6/2009)
What is the difference in SQL 2005 RTM and SQL 2005.

RTM is Release to Manufacturing. The original release version of SQL 2005 before any service packs. Logon triggers were only added in Service pack 2, so you need to install SP2 to use logon triggers

And SP2 is already installed in My SQL server.

Not from the version that you gave.

You said:

I have a SQL server version
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

The version listed there 9.00.1399 is RTM. That SQL server has no service packs. The "(Build 2600: Service Pack 2)" relates to the operating system, Server 2003 SP2, not to SQL Server

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass