June 7, 2010 at 7:37 am
Does anybody know a way to monitor the t-sql which is being run when for example a user login gets mapped to a particular database with db_reader privileges for example.
I´ve been trying to use EVENTDATA() through a DDL server Trigger, but /EVENT_INSTANCE/TSQLCommand return with a NULL value 🙁
What I have now is the time of the ALTER_LOGIN command and who is issuing it. But the cherry on top would be to have the t-sql itself aswell. Should I perhaps look into the Server Broker option ?
June 7, 2010 at 7:43 am
My cent is on the SQL Server Profiler.. THis will clearly let you know the exaclt T-SQL statement being run.. I am not big-shot in Profiler, so probably our other guys will have to comment on this. But meanwhile, you can have a look a the video from Brad McGehee on Profiler here on ssc.com.
Here is the link to the videos : http://www.sqlservercentral.com/Articles/Video/Profiler/
June 7, 2010 at 7:59 am
Yes I´ve also been looking into the Profiler, but always thought that was for temporary auditing, but maybe I could utilize that for permanent auditing.
thanks for the videos, I´ll take a look at them 🙂
June 7, 2010 at 8:20 am
Don't use Profiler, too much impact. Server-side trace is much better for long-term tracing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 7, 2010 at 3:42 pm
This is the code I have been using. It has been working fairly well...
/****** Object: DdlTrigger [ddl_server_Changes] Script Date: 05/24/2010 15:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create TRIGGER [ddl_server_Changes]
ON all server
FOR create_database, drop_database, alter_database,CREATE_LOGIN,alter_login,drop_login
AS
Declare @event varchar(1000)
Declare @date varchar(1000)
Declare @server varchar(1000)
Declare @user varchar(1000)
Declare @db varchar(1000)
Declare @TSQL Nvarchar(MAX)
declare @raw xml
declare @login varchar(1000)
set @raw = eventdata()
set @login = @raw.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)')
SET @event = @raw.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @date = getdate()
SET @server = @raw.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname');
SET @user = CONVERT(sysname, SYSTEM_USER);
SET @db = @raw.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname');
SET @TSQL = eventdata().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');
DECLARE @body2 VARCHAR(3000)
DECLARE @sub VARCHAR(3000)
if @TSQL is null
set @TSQL = 'No Code Available'
if @user is null
SET @user = CONVERT(sysname, current_USER);
if @event = 'drop_database'
begin
SET @sub = 'DDL ALERT: The event ' + @event+ ' has been registered on ' + @server
set @body2 = 'ACTION TAKEN : '+@event +char(13)+
'AFFECTED DATABASE : ' + UPPER(@db) +char(13)+
'DATE OF ACTION: ' + @date +char(13)+
'PERFORMED BY : ' + @user +char(13)+
'CODE EXECUTED : ' + @TSQL +char(13)+
'Please reply to this email with a brief description of the actions taken and ensure the POC has been properly notified.'
end
else if @event = 'create_database'
begin
SET @sub = 'DDL ALERT: The event ' + @event+ ' has been registered on ' + @server
set @body2 = 'ACTION TAKEN : '+@event +char(13)+
'AFFECTED DATABASE : ' + UPPER(@db) +char(13)+
'DATE OF ACTION: ' + @date +char(13)+
'PERFORMED BY : ' + @user +char(13)+
'CODE EXECUTED : ' + @TSQL +char(13)+
'Please reply to this email with a brief description of the actions taken and ensure the POC has been properly notified.'
end
else if @event = 'alter_database'
begin
SET @sub = 'DDL ALERT: The event ' + @event+ ' has been registered on ' + @server
set @body2 = 'ACTION TAKEN : '+@event +char(13)+
'AFFECTED DATABASE : ' + UPPER(@db) +char(13)+
'DATE OF ACTION: ' + @date +char(13)+
'PERFORMED BY : ' + @user +char(13)+
'CODE EXECUTED : ' + @TSQL +char(13)+
'Please reply to this email with a brief description of the actions taken and ensure the POC has been properly notified.'
end
else if @event = 'create_login'
begin
SET @sub = 'DDL ALERT: The event ' + @event+ ' has been registered on ' + @server
set @body2 = 'ACTION TAKEN : '+@event +char(13)+
'AFFECTED LOGIN : ' + UPPER(@login) +char(13)+
'DATE OF ACTION: ' + @date +char(13)+
'PERFORMED BY : ' + @user +char(13)+
'CODE EXECUTED : ' + @TSQL +char(13)+
'Please reply to this email with a brief description of the actions taken and ensure the POC has been properly notified.'
end
else if @event = 'alter_login'
begin
SET @sub = 'DDL ALERT: The event ' + @event+ ' has been registered on ' + @server
set @body2 = 'ACTION TAKEN : '+@event +char(13)+
'AFFECTED LOGIN : ' + UPPER(@login) +char(13)+
'DATE OF ACTION: ' + @date +char(13)+
'PERFORMED BY : ' + @user +char(13)+
'CODE EXECUTED : ' + @TSQL +char(13)+
'Please reply to this email with a brief description of the actions taken and ensure the POC has been properly notified.'
end
else if @event = 'drop_login'
begin
SET @sub = 'DDL ALERT: The event ' + @event+ ' has been registered on ' + @server
set @body2 = 'ACTION TAKEN : '+@event +char(13)+
'AFFECTED LOGON : ' + UPPER(@login) +char(13)+
'DATE OF ACTION: ' + @date +char(13)+
'PERFORMED BY : ' + @user +char(13)+
'CODE EXECUTED : ' + @TSQL +char(13)+
'Please reply to this email with a brief description of the actions taken and ensure the POC has been properly notified.'
end
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBA_ALERTS',
@recipients = 'email@whereiwork.com',
@body = @body2,
@subject = @sub
GO
June 8, 2010 at 4:59 am
Ok and so if you would issue with command:
create login test with password = "123"
Do you get the sql from EventData()?
June 8, 2010 at 7:13 am
No, you don't, becuase it doesn't exist. If you grab the XML code that is shot out by eventdata(), you will see there no SQL code harvested for the create or alter login statments. I am not sure why this is, but I haven't found a way around it as yet. That is why I defaulted the code to read "No Code Avaialble" when TSQL returns nulls.
June 8, 2010 at 7:31 am
This is ths EventData() XML for the create login event
<EVENT_INSTANCE>
<EventType>CREATE_LOGIN</EventType>
<PostTime>2010-06-08T06:28:54.510</PostTime>
<SPID>198</SPID>
<ServerName>CP-ITS-SQL64D02\SQLDEV02</ServerName>
<LoginName>SAIC-US-WEST\x_stevicd</LoginName>
<ObjectName>test12345</ObjectName>
<ObjectType>LOGIN</ObjectType>
<DefaultLanguage>us_english</DefaultLanguage>
<DefaultDatabase>master</DefaultDatabase>
<LoginType>SQL Login</LoginType>
<SID>IO2Bm0y2skKOUXuQi870GA==</SID>
</EVENT_INSTANCE>
this is the EventData() XML for the drop login event
<EVENT_INSTANCE>
<EventType>DROP_LOGIN</EventType>
<PostTime>2010-06-08T06:28:43.653</PostTime>
<SPID>198</SPID>
<ServerName>CP-ITS-SQL64D02\SQLDEV02</ServerName>
<LoginName>SAIC-US-WEST\x_stevicd</LoginName>
<ObjectName>test12345</ObjectName>
<ObjectType>LOGIN</ObjectType>
<DefaultLanguage>us_english</DefaultLanguage>
<DefaultDatabase>master</DefaultDatabase>
<LoginType>SQL Login</LoginType>
<SID>2bew+80YLUO4wPJcHtO+sg==</SID>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>drop login test12345 </CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
TSQLCommand doen't exsist in the Create Event for Logins.
June 8, 2010 at 7:38 am
Don Stevic (6/8/2010)
I am not sure why this is, but I haven't found a way around it as yet.
I can think of one reason. Security. Think what the CREATE LOGIN statement for a SQL login contains.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 8, 2010 at 8:02 am
I thought of that as well since create statment contain passwords. And that does make perfect sense. Alter Login statements also do not record a TSQL statement, but they do not always have passwords in them. It believe it is as you inferred, and it is a security feature and you cannot get the information you wanted from a trigger.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply