http://www.sqlservercentral.com/blogs/brian_kelley/2007/07/09/ddl-triggers-and-create_loginalter_login/ Printed 2016/10/01 11:55AM
DDL Triggers and CREATE_LOGIN/ALTER_LOGIN
2007/07/09I'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 sysdatabasesThen 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().
SQL Server |
Microsoft SQL Server |
SQL Server 2000 |
SQL Server 2005 |
Database Security |
SQL Server Security
| DDL Triggers