• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

Record User ID SQl 2008

Hello,

I'm new to SQL, but I'm trying to transition an access database that used linked tables in SharePoint to an Access front end that uses linked tables in SQL2008 server.

I've used the Access Upscale facility to move the linked tables to a SQL database ... which all worked ok.

I had a problem that the fields that sharepoint used to populate automatically i.e Date modified / created  didn't automatically populate. I've solved this now by using triggers to inset the date depending on if the record is updated / inserted.

The problem I have now is how I go about capturing the USER ID and inserting that into Created By / Modified by... Sharepoint was linked to the AD Address book so was able to resolve the users actual name.

Is it possible to capture this data using a trigger also? - I could pass it as part of the sql statement but would ratehr have the server handle this part to remove the possibility of manipulation.

Thanks
0
bedsingar
Asked:
bedsingar
1 Solution
 
AnujSQL Server DBACommented:
Use SYSTEM_USER, in your inserts or add default  values  "SYSTEM_USER" to creatdby and modifiedby columns .
0
 
bedsingarAuthor Commented:
Ah yes...

Went with:  

UPDATE dbo.API_EVENT_LOG
    SET [Modified By] = SYSTEM_USER
    WHERE ID IN (SELECT DISTINCT ID FROM Inserted)

So that the modified by updates by the created by stays the same.

Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now