trace?

Question

trace?

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Just eyeballing your code so forgive me if this is a rabbit hole but I think if client_net_address is NULL then you end up with something that will anger most XML parsers:

<TR>
<TD>IPAddress</TD>
<TD><local host></TD>
</TR>

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lowell SSC Guru Points: 323518 More actions · Answer 2

opc.three (5/25/2011)
Just eyeballing your code so forgive me if this is a rabbit hole but I think if client_net_address is NULL then you end up with something that will anger most XML parsers:
<TR>
<TD>IPAddress</TD>
<TD><local host></TD>
</TR>

definitely a code improvement to make there, thanks....wasn't looking deep enough to make sure it will be xml/ html compliant...

I'm still testing, but if i get a result in SSMS, I was kind of expecting the same result inside the trigger...but my IP always comes back blank when i remote connect, even though i know the remote address is 192.168.0.55 , either from ipconfig or when i ran that query as a stand alone.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Lowell (5/25/2011)
opc.three (5/25/2011)
Just eyeballing your code so forgive me if this is a rabbit hole but I think if client_net_address is NULL then you end up with something that will anger most XML parsers:
<TR>
<TD>IPAddress</TD>
<TD><local host></TD>
</TR>
definitely a code improvement to make there, thanks....wasn't looking deep enough to make sure it will be xml/ html compliant...
I'm still testing, but if i get a result in SSMS, I was kind of expecting the same result inside the trigger...but my IP always comes back blank when i remote connect, even though i know the remote address is 192.168.0.55 , either from ipconfig or when i ran that query as a stand alone.

Hmmm.... 2005? 2008? R2?

On R2 you're trigger works great for me! I am getting the IP address to show just fine. When connecting locally using Shared Memory sys.dm_exec_connections shows <local machine>...but when using an explicit IP address I see my IP.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

It is working fine for me in 2005 also but how can i filter this code so that i get email only when the user is connected through "ApplicationName Microsoft SQL Server Management Studio"

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

Something like this inside your trigger should work:

IF ( APP_NAME() LIKE 'Microsoft SQL Server Management Studio%' )

BEGIN

-- send an email

END

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Tara-1044200 SSCoach Points: 15951 More actions · Answer 6

Also i am not getting the original user name, I am getting my own id who ever logs in under "sUserName".

and since all our users connect to sql server directly on the server through RDP I am getting ip address of the server which is why i am not able to find the actual user using the sql account i am tryign to track.

Lowell SSC Guru Points: 323518 More actions · Answer 7

I'm assuming you used my example as a partial model?

my knee jerk reaction was to use EXECUTE AS SELF to avoid permissions issues, and that seems to mask some of the data you are looking for.

according to this BOL article:

http://msdn.microsoft.com/en-us/library/ms190307.aspx
Permissions
--------------------------------------------------------------------------------
Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.

we want the users to not fail in the trigger... so i'd change the query to not select from the sys table:

SELECT

getdate() AS EventDate,

DB_NAME() AS DBName,

HOST_NAME() AS HostName,

APP_NAME() AS ApplicationName,

OBJECT_NAME(@@PROCID) AS ProcedureName,

USER_ID() AS Userid,

USER_NAME() AS UserName,

SUSER_ID() AS sUserid,

SUSER_SNAME() AS sUserName,

IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

IS_MEMBER('db_owner') AS [Is_DB_owner],

IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]

the second issue, sending the email....if you can make sure all users are members of that role [DatabaseMailUser] in msdb, you could remove the EXECUTE AS SELF to get better details about the users themselves.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!