Windows login information

Question

Post reply

Windows login information

deepa_nandan

SSC Enthusiast

Points: 119
More actions
July 16, 2005 at 8:37 am

#88087

We have a ERP package that connects to SQLServer via a common account (same user/password is used) by the ODBC it uses. But recently, our Sarbanes-Oxly auditor wanted us to keep track of who makes certain changes into the database. Is there a way that I can include code into my trigger so that it captures the account that users login to the windows (not SQLServer) since SYSTEM_USER is of no use since it shows same account for all users. Any help is appreciated.
Thanks a lot.
--Raghu

Raghu M Nandan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Try using "select suser_sname()" or "select user_name()"

**ASCII stupid question, get a stupid ANSI !!!**

deepa_nandan SSC Enthusiast Points: 119 More actions · Answer 2

I tried using 'Select suser_sname' and it returned 'sa' and 'Select user_name' returned 'dbo', as the results. Where as, I am logged into Windows as 'rnandan' and this is the return value I am trying to get at.

Thanks but it did not work.

Raghu M Nandan

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

Maybe you should then use a windows api call:

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _

(ByVal lpBuffer As String, nSize As Long) As Long

and insert the user name into the audit table ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

Hello,

I would advise to give a login to Windows group.

Then change the connection string to use integrated authentication, see http://www.connectionstrings.com for syntax.

Then use one of the following:

Select suser_sname(suser_sid())

select suser_sname()

Yelena

Regards,Yelena Varsha

Arun-235202 Old Hand Points: 385 More actions · Answer 5

there r 2 ways u can do,

1.Windows Authentication or use api's or users (simmlar to sushila's post.

2.if u were traking transaction level who has created the transaction(norally all ERP will have this kind of table structure) u can use that fields and write triggers.

all th best

deepa_nandan SSC Enthusiast Points: 119 More actions · Answer 6

Thanks for all your solutions...I will try and will let you guys know how it worked out.

Thanks again

Raghu M Nandan