I've been doing a lot with DDL triggers in the last week or so and pulling out the relevant information from the EVENTDATA() function. One of the things I noticed was that with CREATE_LOGIN and ALTER_LOGIN events, you cannot get back the T-SQL information. This makes sense because these two DDL statements could have a password specified. As a result, SQL Server 2005 blocks the T-SQL from being displayed.

This isn't anything new. SQL Server Profiler has done this for the sp_addlogin, sp_password, and sp_setapprole in SQL Server 2000. And SQL Server Profiler does the same thing in SQL Server 2005. Also, DBCC INPUTBUFFER() (and fn_get_sql()) hide the information. For instance, open a connection to a SQL Server 2000 server and note the SPID.

EXEC sp_addlogin 'TestDropAfterDone', '#Compl3xPassw0rd!'

Then open a second connection and use it to see what DBCC INPUTBUFFER() returns. Use the SPID of the first connection.

DBCC INPUTBUFFER(<SPID>)

Note that the EventInfo just says sp_addlogin. Switch back to the first connection and execute:

SELECT * FROM sysdatabases

Then run the DBCC INPUTBUFFER() command and note the T-SQL query is shown this time around. SQL Server is blocking the sp_addlogin call and only telling us about the stored procedure; we don't get any of the parameters. However, a regular query results in a different behavior. DDL triggers with CREATE_LOGIN and ALTER_LOGIN are going to be the same way as what we saw for sp_addlogin. We cannot get the T-SQL command. However, we can get the type of operation (CREATE vs. ALTER) as well as the object affected all from EVENTDATA().


Technorati Tags: | T-SQL | SQL Server | Microsoft SQL Server | SQL Server 2000 | SQL Server 2005 | Security | Database Security | SQL Server Security | DDL Triggers