Want to implement "Created By" fields for your custom tables in your SharePoint environment?
Fairly easy to do, once you know how to ;)
The trick is to use a not-so-well-kown SQL Server method called "SYSTEM_USER", this will automatically be filled with the SQL Server authenticated user, whether thats a SQL User or a Windows User.
So, first make a CreatedBy field on your table.
Then create an insert trigger, and use the SYSTEM_USER method to fill the field.
CREATE TRIGGER MyTable_FillCreatedBy
ON dbo.MyTable
FOR INSERT
AS
UPDATE MyTable SET CreatedBy = SYSTEM_USER WHERE ID = (SELECT ID FROM INSERTED)
Then of course, you need to correctly configure your authentication towards your custom database.
In case of SQL Authentication, just set the correct user and config in your application configuration file (such as web.config).
In case of Windows Authentication, its somewhat more complicated.
First, you need to change your connection string to Integrated Windows Authentication.
Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDb;Data Source=127.0.0.1
Make sure the 'Identitity Impersonate' tag is also set to true!
Then you need to add the necessary user accounts as logins to your SQL Server. Its generally speaking a good idea to work with a *group*, give that group access to your database and add all users that need access to your group.
The nice thing is that SYSTEM_USER is still going to fill in the individual logged-in user even though only the group has access to the SQL Server.
One big gotcha with Windows Authentication is that it won't work when your SQL Server is on a different server than your Sharepoint (or more in general, IIS) server, at least not when your network is configured with NT Authentication as opposed to Kerberos. There are a few basic fixes for this:
1) Use SQL Authentication ;)
2) Setup Sharepoint to use Kerberos authentication (not so trivial)
3) Setup a 'trust' relationship between the two servers (fiddly)
