Getting the current user in a SQL Server stored procedure

Question

I have an instance of SQL Server 2008 Express R2 authenticated from Active Directory. Users have entries associated with their AD account stored in the database. I would like to have stored procedures for retrieving my records.

Thus, the procedure can make a SELECT * FROM PurchaseOrders WHERE uid = $userid request

How do I know the $userid property in my AD profile?

Thanks!

Answer 1

Generally you want to get

 SELECT SUSER_NAME() 

from SQL Server - the connected user in the format DOMAIN\UserName .

Usually you do not have direct access to AD from the SQL Server computer to capture some bits from there .... if you cannot work with the DOMAIN\User Name value, it is best to go through this information from your calling application into a stored procedure.

Answer 2

 SELECT * FROM PurchaseOrders WHERE uid = CURRENT_USER 

or

 SELECT * FROM PurchaseOrders WHERE uid = SUSER_NAME()