Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    84

    Unanswered: Foreign keys on System tables

    I know altering the schema of system tables is a big no-no, but I was wondering if setting up a table that has foreign keys pointing to a system table is bad.

    Basically what I'm refering to is in some cases I have CreationDate and CreatedBy fields in my tables that correspond to GETDATE() and USER_NAME() functions in insert statements....I want the CreatedBy field to be a valid SQL server DB username ... and not some unchecked string value (SYSNAME actually)

  2. #2
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Fortunately, altering system table schema is not possible. No telling the wild posts we'd see on this forum if it was possible - eg My SQL Server is toast, what went wrong?.

    If a user gets dropped and you had a foreign key set up like you are suggesting, you would need to invalidate all the rows that had CreatedBy = dropped user.

    What do you consider to be a valid SQL server DB username?

  3. #3
    Join Date
    Oct 2003
    Posts
    84
    Well I tried setting it up and it doesn't let me (should have tried it before posting). I meant referencing the Name or UID field in the SysUsers
    table of current Database....Maybe an insert/delete trigger is the way to go?
    and in case the user is dropped from the database the userName still stays there (shouldn't cause problems)

  4. #4
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Without knowing your security model, it is difficult to suggest what would work best without making a bunch of assumptions.

    Are you relying on integrated security? If you are, is each user authenticated with their DomainName\UserName? If they are, you can use System_User.

    If not, does your application authenticate users? If it does, you can pass the authenticated user name to your Insert/Update/Delete stored procedures and store that value in the corresponding column (CreatedBy, UpdatedBy, DeletedBy).

Posting Permissions

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