SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in


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.
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

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


Posted by Anonymous on 16 July 2007
In two blogs I follow, Andy Leonard's blog on SQLBlogs.com and Sheeri Kritzer's The MySQL She-BA, the...
Leave a Comment

Please register or log in to leave a comment.