July 17, 2002 at 6:03 am
Hi,
I am using SQL Server Profiler for the first time and I am facing some problems with it...
I need to track the users who connect to my database(through SQL Query Analyser)and the TSQL commands issued by them...so I created a trace with two events "Sessions" and "T-SQL"...and it was working fine and capturing the NT User IDs of the users who log into to the database..
But.....whenever someone from a domain different from the domain the database is located in logs into the server then the column NT User ID remains blank...
Is the trace applicable only for users of the same domain??Have I made some basic mistake in defining the trace(i.e have I forgotten any options that I should have selected)??Can the trace work for users who login from different domains??
Will anyone make sense of this message?? 🙂
Thanks in Advance
winash
July 17, 2002 at 6:30 am
Hi, there is also a column loginname which captures sql login or a windows login in the form domain\username
regards,
Klaas-Jan
July 17, 2002 at 6:37 am
Hi,
> Is the trace applicable only for users of the same domain?
No, don't think there would be such a limitation with this wonder...
> Have I made some basic mistake in defining the trace(i.e have I forgotten any options that I should have selected)
I guess so...Check the 'HostName' option in the 'Data Columns' tab
> Can the trace work for users who login from different domains??
I feel it should as there is an option 'NTDomainName' in the 'Data columns' tab.
I am not much of a SQL Prof. Just trying to get my hands on it.
I would like to hear more on the third question...I probably will also try that.
Hope this helps you...
IF U DON'T SEEK PERFECTION, U CAN NEVER REACH EXCELLENCE!!!
SD
Regards,
Sachin Dedhia
July 18, 2002 at 3:14 pm
Hi,
I think the version of Profiler I am using is a bit outdated...I am using SQL Server 7.0 and the Profiler version is 7.000.623
I arrived at this conclusion as :
i) I cant find a "loginname" column..the only related columns available are "NT User Name" and "SQL User Name"...
ii) There is no "hostname" option available in the Data Columns tab...
iii) There is no "NT Domain Name" column in the Data Columns tab...however(as mentioned earlier) there is a "NT User Name" column...
As an alternative(till I understand whats going on with Profiler) I plan to use a trigger to populate a history table with the host_name() value whenever data is updated in the table I want to trace...but this only gives me the machine name from where the updation has been done...
which (finally!!!) brings me to my question -Can I also capture the NT User ID this way??(i.e is there any command to obtain the NT User ID like the Host_Name() command)
TIA
winash
July 18, 2002 at 6:00 pm
Look at the functions USER, USER_NAME, and CURRENT_USER.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 18, 2002 at 6:15 pm
Hi,
I have checked with these commands...they still give me the SQL Login details(like dbo or any other SQL Login that is being used for access)and not the NT User details...
Is this because the connection to SQL Server is through SQL Authentication and not through NT Authentication??
I think I'll use sp_who2 to further complicate my trigger... 🙂
July 18, 2002 at 6:25 pm
That is the only way you will see the SQL login account. A user must be loging in under Windows Authentication for you to see the Windows account domain/name.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 18, 2002 at 6:35 pm
*Finally comprehension dawns upon winash*
Thanks a lot... 🙂
Managing senior programmers is like herding cats.
July 20, 2002 at 2:47 am
Hi,
Well, could not reply 'coz of weekend.
I am working on SQL Server 2000. So I guess these are the additions in Profile for SQL Server 2000.
Anyway, you made your way...
Regards,
Sachin Dedhia
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply