SQLTeam.com | Weblogs | Forums

Get Window account from SQL Authentication account


#1

I am not even sure if this is possible or if I am will be asking this correctly but I am going to ask anyway.

If I log into a server with a SQL Authentication account, is there a way to get the Window Domain Account when calling a Stored Procedure (SP)?

So for example, because I run SSMS in a virtual environment, I have been using something like this to launch SSMS.

C:\Windows\System32\runas.exe /NetOnly /User:MyDomain\BabyAqua "C:\Program Files\Microsoft SQL Server"

Once SSMS launches, I use a SQL Authentication Account such as
User: HelloWorld
Password:

So what I am trying to get is Domain\BabyAqua when I run a SP. I read something about Kerobose but I am not going to ask about that yet.

Basically, we will always log in and call SPs from a SQL Authentication account because this is a vendor database. But when we call the SP, I am trying to find a way to track the Window account of who is calling the SPs.


#2

Grant impersonate to the SQL Server account on the Windows account. In your procedure add with execute as 'the windows account'. This security scheme can also be used to allow different or elevated permissions for certain procedures. For example a user may have db_datareader role only but through stored procedures still be able to insert, update and delete.


#3

If you connect to SQL Server via SQL Authentication there is no way to obtain the Windows Account name of the caller, that information is simply not made available to SQL Server. The best you can do is use a custom application to collect the information and pass it as a parameter in a stored proc, but anyone who knows the SQL login details can use SMSS to connect and pass through "fake" details to bypass that.

The only way to get trusted information about the Windows account is to use Windows Authentication. If that simply isn't an option, then your next best bet is to give each user who needs to connect their own SQL Authentication account so that you can track that to know who is doing what.


#4

Joe and Andy, thank you for your reply. I have been really busy looking into this. At least the provided information from both of you will point me in the right direction and not waste time into looking into irrelevant information.