Blast


I'm wanting to do some auditing with sql 2k and wish to get the users first and last name of the windows account to log into a table. You can easily access the windows account name by using the System_User keyword. However, is it possible to get the first and last name of the system_user If so, how

Thanks.






Re: sql server 2k auditing

Blast

I have read through these articles but they didn't answer my question.

When an Insert, Update or delete statement is called on a table, I want to log this activity in table called Audit. In the Audit table, I wish to insert the first and last name of the windows account. Is this possible







Re: sql server 2k auditing

Amit Banerjee - MSFT

You will get only the user name. The first and last name is something that would be found from your AD users table. So, to achieve this sort of functionality you would have to write a DML trigger to perform a AD lookup and use APIs to resolve the username and find the first and last name.

SQL Server also queries your AD in the background in case you are using Windows Accounts.






Re: sql server 2k auditing

Blast

Can you either give or link to some documentation with an example of how to accomplish this






Re: sql server 2k auditing

Arnie Rowland

Quite honestly, I wouldn't go down that path.

By capturing the system_user, you can easily relate to the AD account when you have audit needs to identify the individual by first and last name.

Of course, your organization could have choosen to use Firstname_LastName as the network login and your troubles would be easily solved.






Re: sql server 2k auditing

Blast

Seems like this shouldn't be too difficult to do... does anyone have an article or code snippet on how to take the System_User information and look up the first and last name of the account

Basically, what I want to do is capture the login information so the users can query who created items in the database. How else would you do this

If you think of a relational database you would store Employee Information in the employee table. But this information isn't in the employee table its stored in the active directory database. So I need to perform lookup operations...can't be hard






Re: sql server 2k auditing


Re: sql server 2k auditing

Blast

Looks like a good start. In this particular blog it says that ldap has a 1000 row limit which shouldn't be a problem if you use a where clause that specifies the windows account name, since that should unique no






Re: sql server 2k auditing

Arnie Rowland

I think that your assumption would prove correct.