DDL triggers on create Logins

Hi all,
I have setup a DDL trigger for server logins. If I drop a user my trigger fires off an email with the EVENTDATA().value. If I create a new user the Eventdata is null. I am using the code below:
TIA,
Joe

DROP TRIGGER ddl_trig_login
ON ALL SERVER
GO
CREATE TRIGGER ddl_trig_login
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
declare @.user varchar(100),@.event varchar(1000), @.subj varchar(100)
select @.user = SUSER_SNAME()
Select @.subj = 'Login Event Issued from '+@.user
select @.event = @.user+' committed the following event on ENSQLD1_2005: '+
isnull((SELECT EVENTDATA().value'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')),' ')+' Please email the group with the SIR# for this action.'
EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'SQL2005mail',
@.recipients = 'j.f@.myco.com',
@.body = @.event,
@.subject = @.subj
;

TSQLCommand text is not available for CREATE LOGIN, so you won't be able to see the data. But you can always see other useful information through EVENTDATA.value(). For example

select @.event = @.user+' committed the following event on ENSQLD1_2005: '+
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') +
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)') +
...

|||Thank you,
I was able to trap the new user name and the create login eventtype. Now I have the triggers emailing me on any drop of user or create of one.

Do you know what event gets fired off when I change the permissions of a user? I thought it would be Alter_login, but when I grant rights to a DB to a user I dont get an email.

Thanks again!
Joe|||

> Do you know what event gets fired off when I change the permissions of a

> user?

Well, did you try running profiler and inspect the commands sent?

DDL create user in T-SQL stored proc/trigger

Hello SQL Server programming gurus:

I am trying to create a trigger that fires after a user logon and logoff and does the following:

creates a new user
deletes data from old temp table

Then I need to create a stored procedure that executes dynamically to
drop old users
remove the old user account access

We are running SQL Server 2000.
Since I am new to T-SQL programming could anyone help point me in the right direction? Can I write dynamic SQL in a trigger to do these things?I really don't think you want to try that using a trigger. It sounds like a really bad idea to me.

First and foremost, I don't know of any way to launch a trigger on a login or logout event. You could probably approximate this using either SQL Profiler or a "watchdog" process, but the fundamental idea isn't supported by SQL Server itself, so you'll need some kind of "helper" to get the job done.

Next, I'd be really leary of having users created "on the fly" by anyone, much less what you seem to be describing where every login has the ability to create users! I can't imagine a scenario where that idea would appeal to me, unless it was to torment some dba that was going to inherit that code after I'd left. The thought positively gives me the willies!

The temp tables really aren't any problem... SQL Server simply drops temp tables after the user logs off. You really don't need to do any "care and feeding" of them, they are completely expendable.

If you can explain what you are trying to do (in English, not Transact-SQL), I'd be willing to bet that someone here can offer you some ideas that will help a bunch!

-PatP