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().
Technorati Tags: | T-SQL | SQL Server | Microsoft SQL Server | SQL Server 2000 | SQL Server 2005 | Security | Database Security | SQL Server Security | DDL Triggers