SQL 2005 & Login Triggers?

[JoeyD]

With SQL 2005, did Microsoft introduce login triggers? I seem to
remember hearing something about it but, I don't remember exactly what
I heard.

Thanks,
JD

[Razvan Socol]

Hi, Joey

The new thing in SQL 2005 are DDL triggers, including triggers for:
- CREATE_LOGIN (Applies to CREATE LOGIN statement, sp_addlogin,
sp_grantlogin, xp_grantlogin, and sp_denylogin when used on a
nonexistent login that must be implicitly created.)
- ALTER_LOGIN (Applies to ALTER LOGIN statement, sp_defaultdb,
sp_defaultlanguage, sp_password, and sp_change_users_login when
Auto_Fix is specified.)
- DROP_LOGIN (Applies to DROP LOGIN statement, sp_droplogin,
sp_revokelogin, and xp_revokelogin.)

If you want a trigger that fires when a user logs-in on SQL Server
(i.e. when a new connection is created), I'm afraid that it's nothing
new here, so you will have to use the clasic tools: SQL Profiler. If
you want to do this programatically, I think you can create a trace
(see Books Online for more info).

Razvan

JoeyD wrote:

With SQL 2005, did Microsoft introduce login triggers? I seem to
remember hearing something about it but, I don't remember exactly what
I heard.

Thanks,
JD

[JoeyD]

Razvan,
Thank you for the info. What you've said has helped to refresh my
memory.

JD
Razvan Socol wrote:

Hi, Joey

The new thing in SQL 2005 are DDL triggers, including triggers for:
- CREATE_LOGIN (Applies to CREATE LOGIN statement, sp_addlogin,
sp_grantlogin, xp_grantlogin, and sp_denylogin when used on a
nonexistent login that must be implicitly created.)
- ALTER_LOGIN (Applies to ALTER LOGIN statement, sp_defaultdb,
sp_defaultlanguage, sp_password, and sp_change_users_login when
Auto_Fix is specified.)
- DROP_LOGIN (Applies to DROP LOGIN statement, sp_droplogin,
sp_revokelogin, and xp_revokelogin.)

If you want a trigger that fires when a user logs-in on SQL Server
(i.e. when a new connection is created), I'm afraid that it's nothing
new here, so you will have to use the clasic tools: SQL Profiler. If
you want to do this programatically, I think you can create a trace
(see Books Online for more info).

Razvan

JoeyD wrote:

With SQL 2005, did Microsoft introduce login triggers? I seem to
remember hearing something about it but, I don't remember exactly what
I heard.

Thanks,
JD

[Razvan Socol]

In SQL Server 2005 Service Pack 2, there is a new feature for Logon
Triggers. Logon triggers fire after the authentication phase of logging
in finishes, but before the user session is actually established. See
the updated Books Online for more information.

Razvan
PS. SQL Server 2005 Service Pack 2 is now in the "Community Technology
Preview" phase.

Razvan Socol wrote:

Hi, Joey

The new thing in SQL 2005 are DDL triggers, including triggers for:
- CREATE_LOGIN (Applies to CREATE LOGIN statement, sp_addlogin,
sp_grantlogin, xp_grantlogin, and sp_denylogin when used on a
nonexistent login that must be implicitly created.)
- ALTER_LOGIN (Applies to ALTER LOGIN statement, sp_defaultdb,
sp_defaultlanguage, sp_password, and sp_change_users_login when
Auto_Fix is specified.)
- DROP_LOGIN (Applies to DROP LOGIN statement, sp_droplogin,
sp_revokelogin, and xp_revokelogin.)

If you want a trigger that fires when a user logs-in on SQL Server
(i.e. when a new connection is created), I'm afraid that it's nothing
new here, so you will have to use the clasic tools: SQL Profiler. If
you want to do this programatically, I think you can create a trace
(see Books Online for more info).

Razvan

JoeyD wrote:

With SQL 2005, did Microsoft introduce login triggers? I seem to
remember hearing something about it but, I don't remember exactly what
I heard.

Thanks,
JD

[JoeyD]

Thanks Razvan - that is something my employer has been asking me about.
SP2 ? We've only got a couple of our SQL 2005 installs up to SP1.

I'll be checking it out.

thanks again,
JoeyD

Razvan Socol wrote:

In SQL Server 2005 Service Pack 2, there is a new feature for Logon
Triggers. Logon triggers fire after the authentication phase of logging
in finishes, but before the user session is actually established. See
the updated Books Online for more information.

Razvan
PS. SQL Server 2005 Service Pack 2 is now in the "Community Technology
Preview" phase.

Razvan Socol wrote:

Hi, Joey

The new thing in SQL 2005 are DDL triggers, including triggers for:
- CREATE_LOGIN (Applies to CREATE LOGIN statement, sp_addlogin,
sp_grantlogin, xp_grantlogin, and sp_denylogin when used on a
nonexistent login that must be implicitly created.)
- ALTER_LOGIN (Applies to ALTER LOGIN statement, sp_defaultdb,
sp_defaultlanguage, sp_password, and sp_change_users_login when
Auto_Fix is specified.)
- DROP_LOGIN (Applies to DROP LOGIN statement, sp_droplogin,
sp_revokelogin, and xp_revokelogin.)

If you want a trigger that fires when a user logs-in on SQL Server
(i.e. when a new connection is created), I'm afraid that it's nothing
new here, so you will have to use the clasic tools: SQL Profiler. If
you want to do this programatically, I think you can create a trace
(see Books Online for more info).

Razvan

JoeyD wrote:

With SQL 2005, did Microsoft introduce login triggers? I seem to
remember hearing something about it but, I don't remember exactly what
I heard.
>
Thanks,
JD