SQL Server Security - How to return Current User

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Server Security - How to return Current User

Author

Topic

Lester Vincent
Starting Member

22 Posts

Posted - 2003-12-23 : 18:30:21

Hi everyone. Happy Christmas.
My problem is as follows: -
Have just started upsizing from Access .MDb to .ADP and feeling my way.

If doing VBA coding in secured Access.MDB module, I have performed transaction-logging of data changes in bound forms, using After-Update and After_Insert events to add records to a TransLog table, thus creating a history of who changed what, roughly as follows: -

!(Text summary of data changes,etc)
!DateTime=Now
!User=CurrentUser 'In access CurrentUser() returns the logged in user's name
.Update

In the Access .ADP file, using SQL Security, I need a feature similar to "CurrentUser" to capture user who made the changes to data.

Is there a way of doing this in SQL? If so, how is it best achieved.

I am proposing to store the data in one table, for all editing in the database.

Hope you can help.

Lester Vincent
Sydney, Australia

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-23 : 18:35:38

Have a look at USER_NAME() function in Books Online.

Lester Vincent
Starting Member

22 Posts

Posted - 2003-12-23 : 18:43:13

Thanks Ehorn. There had to be something to cover it.
Cheers,

lester Vincent

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-12-23 : 18:45:12

select SYSTEM_USER will also work for sa and system administrators.
USER_NAME() will return dbo.

You should consider TRIGGERS AND putting this logic on the backend.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-23 : 19:11:28

ValterBorges has a very good point regarding audit trails at the database level.

If a user changes data outside of you application it will not be logged.

If you are interesting in learning more about triggers there are many good articles on this site.

Also have a look at http://www.nigelrivett.net.

Nigel provides good information and sample code for audit type triggers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources