http://www.sqlservercentral.com/blogs/brian_kelley/2007/07/09/ddl-triggers-and-create_loginalter_login/

Printed 2014/09/02 04:01AM

DDL Triggers and CREATE_LOGIN/ALTER_LOGIN

2007/07/09

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

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.