SQL Server Profiler help needed...

  • winash

    SSChampion

    Points: 11446

    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

  • Klaas-Jan

    SSCrazy

    Points: 2297

    Hi, there is also a column loginname which captures sql login or a windows login in the form domain\username

    regards,

    Klaas-Jan

  • Sachin Dedhia

    SSCrazy

    Points: 2104

    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

  • winash

    SSChampion

    Points: 11446

    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

  • Antares686

    SSC Guru

    Points: 125444

    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)

  • winash

    SSChampion

    Points: 11446

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

  • Antares686

    SSC Guru

    Points: 125444

    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)

  • winash

    SSChampion

    Points: 11446

    *Finally comprehension dawns upon winash*

    Thanks a lot... 🙂

    Managing senior programmers is like herding cats.

  • Sachin Dedhia

    SSCrazy

    Points: 2104

    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 9 (of 9 total)

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