Getting the current user in SQL Server stored procedure

Question

I have a SQL Server 2008 Express R2 instance authenticating from Active Directory. Users have records associated with their AD account stored in a database. I would like to have stored procedures for them to retrieve their records.

So the procedure might make the query SELECT * FROM PurchaseOrders WHERE uid = $userid

How do I find out $userid, a property in their AD profile?

Thanks!

Answer 1

Typically, you'd want to get the

SELECT SUSER_NAME()

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

You don't typically have direct access to AD from a SQL Server machine to grab some bits from there.... if you can't work with the DOMAIN\User Name value, your best bet would be to pass in that information from your calling application to your stored procedure.

Answer 2

SELECT * FROM PurchaseOrders WHERE uid = CURRENT_USER

or

SELECT * FROM PurchaseOrders WHERE uid = SUSER_NAME()