Set the workstation ID to the username and pass it to the Audit Trigger

Posted on 2009-04-18
Last Modified: 2013-11-07


I have set my workstation Id to the current logged in userID to track insert, updates, and deletes.  Thing is, when I step through the code,  I can see that it's correct.  When I chekl the audit Table, it has my machine name or machine name/instance name no matter what.  What am I doing wrong?
private MembershipUser mu;

            private string UserID;           

            mu = Membership.GetUser();

            UserID = mu.ProviderUserKey.ToString();

            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["FI"];

            if (null != settings)


                // Retrieve the connection string.

                string connectString = settings.ConnectionString;

                // Create a new SqlConnectionStringBuilder based on the

                // partial connection string retrieved from the config file.

                SqlConnectionStringBuilder builder =

                    new SqlConnectionStringBuilder(connectString);

                builder.WorkstationID = UserID;

                SqlConnection cn = new SqlConnection(builder.ToString());




Open in new window

Question by:malibuncran
    LVL 25

    Accepted Solution

    The .NET Framework library is possibly overriding the WSID attribute..

    Step through the code and immediately after the connection is established, run the sp_who2 query using SQL Query Window to check the attributes for this connection..

    If you have many connections, then you could run a quick query from code to find out the SPID for the current connection -



    Author Comment

    Hi, thanks for replying. I really appreciate it.  

    I was able to see my 'username' as the host name for one of the returned rows.  But, according to the date stamp, seconds later it goes back to the machine or machine/windows username.  

    The SPID is the same each time, so I don't need to query for that.  What else can I do to make it 'stick' to the Workstation ID I assign it.  

    I've upped the points in to show my gratifications.  

    Author Comment

    After it connects and

    51         sqlUname  'assigned uname'      dbName       04/21 13:23:16  .Net SqlClient Data Provider        
    52   NT AUTHORITY\SYSTEM       machineName   ReportServer$SQL2005  04/21 13:23:18  Report Server      

    This is the SPID,  Login Name (from web.config), host name, db name, last batch,  and prog name.  These two are always right behind one another.  

    When the trigger runs though, the host name is not correct.

    Author Comment

    Ok, I added a 'test' workstation name to the connect string and it recorded successfully.  But it still is not taking my value from the code.  Let me rearrange the code.

    Author Comment

    I removed the user name and password from the connection string and passed them to the the builder via the code.  The connection string im my web.config is definitely over riding my code because I am getting 'Login failed for user ''. The user is not associated with a trusted SQL Server connection. '  which is what's expected if there is no uname and pword in the connection string.

    Author Comment

    I figured out my problem.  I was using a code generator whose default is to use what's in the .config file.  I 'over rode' the default.  

    It works now.

    Author Closing Comment

    Ir lead me to my solution.  Thanks.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now