How could I detect who inserted a new record?

[jcrivera]

Greetings.
Besides using triggers, is there a way to monitor which user inserted a new record in a table?

Please advise.

J.C.

 

[foxdev]

You can create a column ("username", for example), then set a default on it of user_name()

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

For SQL Server 7.0 and before, I would use Suser_Name(). But, as I understand it, this will be going away in 2000.

Also, track the time they inserted/updated it as well. Put a default column ("dt_changed" for example) and set its default value to GetDate(). This can be an invaluable tracking tool.

Tom

 

[jcrivera]

suser_name() would be a good alternative, but we're next to migrate to SQLS 2000. :-(

However, under what circumstances user_name() would return another value besides 'dbo'? At least that's what I'm getting when I attempt a SELECT user_name()

Please advise.

J.C.

 

[TomSark]

User_Name() returns the database account for the currently connected user.

Suser_Name() returns the login account for the currently connected user.

When you connect to SQL Server, your Server login (suser_name) doesn't necessarily have to the same account name as the database account you are using (user_name). Case in point: sa login account becomes dbo on each database it's connected to.

If you aren't a member of sysadmin or logged in as sa, or aren't a db_owner, then you won't become dbo in the database account.

Create an account on the database, call it Tom ;-) for example, connect using that account via SQL Server authentication, then select User_Name(). It should come back as Tom.

Tom