Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Record User ID SQl 2008

Posted on 2013-01-03
2
426 Views
Last Modified: 2013-01-03
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
Comment
Question by:bedsingar
2 Comments
 
LVL 15

Accepted Solution

by:
Anuj earned 500 total points
ID: 38740040
Use SYSTEM_USER, in your inserts or add default  values  "SYSTEM_USER" to creatdby and modifiedby columns .
0
 

Author Comment

by:bedsingar
ID: 38740075
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

861 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