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