CODEWITHSUNDEEP
sql-serverstored-proceduresactive-directory
Mr. Wily
Mr. Wily

Reputation: 113

Getting the current user in SQL Server stored procedure

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!

Upvotes: 11

Views: 39167

Answers (2)

Michaël
Michaël

Reputation: 6734

SELECT * FROM PurchaseOrders WHERE uid = CURRENT_USER

or 

SELECT * FROM PurchaseOrders WHERE uid = SUSER_NAME()

Upvotes: 2

marc_s
marc_s

Reputation: 755093

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.

Upvotes: 16

Related Questions