Solved

Record User ID SQl 2008

Posted on 2013-01-03
2
418 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
This query failed in sql 2014 5 29
SQL Help 27 40
TSQL - How to declare table name 26 28
transaction in asp.net, sql server 6 31
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

813 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now