Post reply

SQL Server NTUSERNAME Tracing Problem

  • January 18, 2008 at 2:15 am

    #95701

    Hi,

    Brand new member on the forum.

    Yesterday I met a very strange problem while using the SQL SERVER Profiler. I opened the SQL Server profiler to check whether some users were accessing the database so that I could update the website.

    While I was watching the logs I noticed that there was a username (NTUSERNAME) which I knew was not accessing the system because he uses the system from my own office. Then I noticed that what SQL Server was doing was that while it was running a set of stored procedures under a particular username (ex : user1), it was running 4 particular stored procedures under a username who was not logged in (ex: user2) for the same user (user1). I do not know how this can happen since the NTUSERNAME is something which SQL Server authenticates and something I do not control.

    I would appreciate if someone helps me

    Thanks

  • January 21, 2008 at 1:53 am

    #770062

    It seems that no one replied.

    I am attaching a screenshot of the problem so that maybe it would be more explained.

    Where there is the username conr it should have been joh.... Everytime SQL Server was running those 4 stored procedure it was running them under conr instead of joh.

  • January 21, 2008 at 4:35 am

    #770085

    what service account are you using for :

    - SQLServer

    - SQLAgent

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 21, 2008 at 4:45 am

    #770088

    Hi,

    Is there any way to find this information from Enterprise Manager since I am not the administrator of the server where this database resides.

    However I am the DBA for this database. Moreover I cannot consider myself as an expert on database administration.

    Thanks

  • January 21, 2008 at 4:52 am

    #770090

    If you have a remote desktop connection available, you can use EM, rightclick the servername and select properties;

    In the security tab, you can see the account that is being used.

    (on W2K3 systems you do not get that info when just connecting from your client workstation)

    -- your userinfo / groupmembership

    SELECT USER_ID() AS [USER_ID]

    , USER_NAME() AS [USER_NAME]

    , SUSER_ID() AS [SUSER_ID]

    , SUSER_SNAME() AS [SUSER_SNAME]

    , IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin]

    , IS_MEMBER('db_owner') AS [Is_DB_owner]

    , IS_MEMBER('db_datareader') AS [Is_DB_Datareader]

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 21, 2008 at 4:58 am

    #770093

    Hi,

    SQL Server is using Windows Authentication.

    In my Web Application I am also using Windows Authentication since this system I am using is in an intranet and every person has an Active Directory account.

    Thaks

  • January 21, 2008 at 5:04 am

    #770095

    Could there be a sql agent job running under his username?

    Is there difference in hostnames?

  • January 21, 2008 at 5:14 am

    #770096

    can you post the result of "sp_who2" (standard system sproc ) ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 21, 2008 at 5:45 am

    #770103

    I do not think that there is an SQL Server agent job under his username. What I found most strange is that only those 4 stored procedures were running under that particular username.

    When I logged in myself and ran the same stored procedure the correct username and therefore mine was ok.

    Underneath you can find the results of the stored procedure sp_who2. Please note that I changed our domainname to domainname.

    1 ,BACKGROUND ,sa, ., . ,NULL,LAZY WRITER ,0,0,12/18 07:19:57, ,1

    2 ,sleeping ,sa, ., . ,NULL,LOG WRITER ,0,0,12/18 07:19:57, ,2

    3 ,BACKGROUND ,sa, ., . ,master,SIGNAL HANDLER ,0,0,12/18 07:19:57, ,3

    4 ,BACKGROUND ,sa, ., . ,NULL,LOCK MONITOR ,0,0,12/18 07:19:57, ,4

    5 ,BACKGROUND ,sa, ., . ,master,TASK MANAGER ,0,42,12/18 07:19:57, ,5

    6 ,sleeping ,sa, ., . ,NULL,CHECKPOINT SLEEP,0,2262,12/18 07:19:57, ,6

    7 ,BACKGROUND ,sa, ., . ,master,TASK MANAGER ,0,0,12/18 07:19:57, ,7

    8 ,BACKGROUND ,sa, ., . ,master,TASK MANAGER ,0,0,12/18 07:19:57, ,8

    9 ,BACKGROUND ,sa, ., . ,master,TASK MANAGER ,0,0,12/18 07:19:57, ,9

    10 ,BACKGROUND ,sa, ., . ,master,TASK MANAGER ,0,19,12/18 07:19:57, ,10

    11 ,BACKGROUND ,sa, ., . ,master,TASK MANAGER ,0,0,12/18 07:19:57, ,11

    12 ,BACKGROUND ,sa, ., . ,master,TASK MANAGER ,0,0,12/18 07:19:57, ,12

    13 ,BACKGROUND ,sa, ., . ,master,TASK MANAGER ,0,0,12/18 07:19:57, ,13

    14 ,BACKGROUND ,sa, ., . ,master,TASK MANAGER ,0,0,12/18 07:19:57, ,14

    15 ,BACKGROUND ,sa, ., . ,master,TASK MANAGER ,0,5,12/18 07:19:57, ,15

    16 ,BACKGROUND ,sa, ., . ,master,TASK MANAGER ,0,7,12/18 07:19:57, ,16

    17 ,BACKGROUND ,sa, ., . ,master,TASK MANAGER ,0,19,12/18 07:19:57, ,17

    51 ,sleeping ,NT AUTHORITY\SYSTEM,FTP , . ,msdb,AWAITING COMMAND,78,14,12/18 07:20:27,SQLAgent - Generic Refresher,51

    52 ,sleeping ,domainname\roeg,FTP , . ,MProject,AWAITING COMMAND,172,3,01/21 13:34:05,.Net SqlClient Data Provider,52

    53 ,sleeping ,domainname\juazzo,FTP , . ,MProject,AWAITING COMMAND,78,0,01/21 13:36:30,.Net SqlClient Data Provider,53

    54 ,sleeping ,FTP\ASPNET,FTP , . ,ReportServer,AWAITING COMMAND,0,0,01/21 13:36:20,Report Server ,54

    55 ,sleeping ,domainname\juazzo,FTP , . ,MProject,AWAITING COMMAND,219,0,01/21 13:36:30,.Net SqlClient Data Provider,55

    56 ,sleeping ,NT AUTHORITY\SYSTEM,FTP , . ,msdb,AWAITING COMMAND,35060,9,01/21 13:37:02,SQLAgent - Alert Engine ,56

    57 ,sleeping ,NT AUTHORITY\NETWORK SERVICE,FTP , . ,ReportServer,AWAITING COMMAND,15,0,01/21 07:22:36,Report Server ,57

    58 ,sleeping ,domainname\markaf,ICT16-01, . ,master,AWAITING COMMAND,16,0,01/21 13:35:22,MS SQLEM ,58

    59 ,sleeping ,domainname\ctonl,FTP , . ,MProject,AWAITING COMMAND,9734,7,01/21 13:36:31,.Net SqlClient Data Provider,59

    60 ,sleeping ,domainname\dmusc,FTP , . ,MProject,AWAITING COMMAND,31,0,01/21 13:36:03,.Net SqlClient Data Provider,60

    61 ,sleeping ,domainname\emerc,FTP , . ,MProject,AWAITING COMMAND,47,0,01/21 13:36:57,.Net SqlClient Data Provider,61

    62 ,sleeping ,domainname\ctonl,FTP , . ,MProject,AWAITING COMMAND,9129,1,01/21 13:36:34,.Net SqlClient Data Provider,62

    63 ,sleeping ,domainname\aons,FTP , . ,MProject,AWAITING COMMAND,1718,0,01/21 13:34:26,.Net SqlClient Data Provider,63

    65 ,sleeping ,domainname\ctinev,FTP , . ,MProject,AWAITING COMMAND,1408,0,01/21 13:37:12,.Net SqlClient Data Provider,65

    66 ,sleeping ,domainname\aons,FTP , . ,MProject,AWAITING COMMAND,0,0,01/21 13:34:24,.Net SqlClient Data Provider,66

    67 ,RUNNABLE ,domainname\mkaf,IT1-01, . ,master,SELECT INTO ,31,3,01/21 13:37:08,SQL Query Analyzer ,67

    68 ,sleeping ,NT AUTHORITY\NETWORK SERVICE,FTP , . ,ReportServer,AWAITING COMMAND,0,0,01/21 13:37:09,Report Server ,68

    71 ,sleeping ,domainname\mkaf,FTP , . ,MProject,AWAITING COMMAND,621,0,01/21 12:45:32,.Net SqlClient Data Provider,71

    73 ,sleeping ,domainname\dmusc,FTP , . ,MProject,AWAITING COMMAND,203,0,01/21 13:36:03,.Net SqlClient Data Provider,73 [/size]

  • January 21, 2008 at 6:14 am

    #770108

    - as with many of us, one of the problems is there is no application name '.Net SqlClient Data':crazy: provided by the connections.

    - try to determine the host your suspect connections work from and pay them a visit. Ask your admin to start SQLProfiler and capture the load for a while and walk through the results together.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 21, 2008 at 7:17 am

    #770137

    Hi,

    Thanks for all the info.

    In this case I do not need the application name because there is only one application running and using this SQL Server. So basically I am sure that the same application was running under a different username when it was running those 4 stored procedures.

    Basically I am very confused because of this and I am starting to think that there was an SQL Profiler bug.

    Thanks

Viewing 11 posts - 1 through 11 (of 11 total)

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