Default logged in user to SQL Server column

Question

How can I create a column with a default value of the user that created the row at the time in the table?

I tried system_user, but that shows who is logged in when selecting from the table.

Answer 1

Try something like this:

CREATE TABLE DemoTable
(
    ID INT IDENTITY(1,1), 
    SomeValue VARCHAR(50), 
    CreatedBy VARCHAR(50) 
        CONSTRAINT DF_DemoTable_CreatedBy DEFAULT(SUSER_NAME())
) 

You basically create a default constraint on one of your columns, and you use the SUSER_NAME() function to populate it with the currently logged in user.

When you then insert values into that table:

INSERT INTO dbo.DemoTable(SomeValue) VALUES('Some Value')

and you don't specify an explicit value for CreatedBy, that column is filled with the value from the SUSER_NAME() function, which you can check by selecting from the table:

SELECT * FROM dbo.DemoTable

Read more about SUSER_NAME() and a few related functions (like SUSER_ID()) on TechNet.