I am migrating the BE of an Access app. to SQL server 2012. I need to get the user's login name (Windows Authentication login). This can be done using xp_cmdshell, but, xp_cmdshell is considered dangerous and I wouldn't be able to run it once I deploy the app. to the company servers (currently I have SQL server on my computer and as an admin I can enable xp_cmdshell to run, but IT doesn't allow it in company servers for security reasons).

Another question, is it possible to send data from the logged in user from Access to SQL server? What I need to do is let SQL know the username of the logged in user, then, use it to filter the data on SQL. Idea is that user can only run queries for his data (he can't view other user's data unless he is a manager or an admin (currently the app. in Access logs the user in automatically if his Windows Domain username is found in the user's table, and set's his role found in the Roles table). It is this functionality that is giving me some problems to migrate to SQL.

PS : I created a function that uses the System_User SQL built-in function, this retrieves the SQL login username, but, the app. uses 1 SQL local account to connect to the server, so in essence it doesn't work as I need the Windows Domain account use

but IT doesn't allow it in company servers for security reasons).

You can enable it and run your script and then turn it off.

CURRENT_USER will return the logged in user.

PS : I created a function that uses the System_User SQL built-in function, this retrieves the SQL login username, but, the app. uses 1 SQL local account to connect to the server, so in essence it doesn't work as I need the Windows Domain account username.

Then you need to change the app to connect to the database using a trusted connection.  Otherwise, what you ask is impossible without a change in architecture. Using a trusted connection, your current approach should work for both cases - but be careful about whether you seek the login name or the user name since these are not always the same. 

But when I migrate the function to the productio environment it won't work as Production servers don't run xp_cmdshell.

Current_User returns dbo not the Windows Domain user.

Can you provide a bit more info. on Trusted Connections and how I can use it? Thanks.

select SYSTEM_USER

?

System_User will return the WIndows Domain user ID if the user is using Windows Authentication, but, if he connects via a SQL account then it won't work. Anyway, thanks all for your input. I will force all users to authenticate with their domain accounts and use System_User will then tell me the Domain account.

Hi Ahpitre,

Use the System.Environment class for this.

Check the following link for explanation and example

http://msdn.microsoft.com/en-us/library/1h5xxewc.aspx

And then follow the below link How to exceute .Net Code in Sql Server

http://www.codeproject.com/Articles/19954/Execute-NET-Code-under-SQL-Server