Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007

    Unanswered: Logins and users question

    Ok, let me preface my question with a little info about the application I am working on. I am in the middle of creating a project in VB.Net for my company. We have a data management system for handling sampling data. Now the database design is like so. The application is able to handle multiple "Sites". We create a new database for each site that is create dint eh application. These databases are identical in structure but the data is obviously different.

    Now we don't actually delete any records in this application rather we mark items as deleted instead. This allows us to easily undo any changes that have been made to the data. When a change or delete are made, we record this change so that reports can be printed to show what changes were made and by who. This is all well and good, but my thoughts are this.

    At the moment I am writing lots of VB code to handle adding these records of change and inserting them into the database... What I would like to be able to do is to just create Triggers on the tables to add them. This is something that I know how to do and i feel like it would be the better way to do it except for one thing... here comes the issue...

    I have no way of knowing what user is logged in to my application from within the trigger because the application uses a single login for accessing the database. My thoughts are this... Would I be crazy to think that it would be a good idea to create SQL server logins for each user that is created in the application, giving these users access to only the database that they have been created in? This would allow me to determine who was logged in when the change was made and could then implement recording changes through triggers... I am not a very experienced dba programming is more my speciality. I know how to implement this idea, but I am just wondering if this sort of thing is considered bad practice or if it is something I should consider implementing...

    Sorry for the novel there and thanks for any help or insight.


  2. #2
    Join Date
    Dec 2004
    Madison, WI
    Not sure if this helps but there is the suser_sname() which should return the login name (I usually put this in as the default value for my EnteredBy field and I'm not sure if it will work in your case with the single login but it may be worth investigating.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2007
    Well, that is what I am looking for, but I was wondering if it makes sense to create a SQL server login for each user in my application.

  4. #4
    Join Date
    Jan 2004
    In a large office with bad lighting
    Quote Originally Posted by bmahler
    Well, that is what I am looking for, but I was wondering if it makes sense to create a SQL server login for each user in my application.
    It would seem so if you want to track who is inserting or modifying your data!

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Nov 2002
    We use connection pooling wih a single login to the database

    The users have application security, and they log into the app via a user table, with passwrod verification done by an API to RACF

    Every database call is limited to Stored Procedures

    The Calling App must pass as a parameter to the sproc, the application user that is logged

    Any modification that is done records the app user on the row

    A trigger then moves the before image row to history as is

    The last person making the modification is on the current row

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts