Get the windows account which runs the query?

I'm using sql server 2008 r2.

I logged in to ssms which in turn connects to a remote sql server machine.

im writing a query which writes a file.

I need to know - which windows permissions I should grant to c:\myfolder

is there any select query which can provide me the windows account who is actually finally writes the file ?

It depends, and I'm afraid Martin give you only part of the answer. The credential used to access the file is different whether you logged in with SQL login or a Windows login.

For SQL logins (ie. user and password based connection string) the credential used is either the SQL server process account of the login proxy credential account, if one is set up:


The information stored in a credential enables a user who has
connected to SQL Server by way of SQL Server Authentication to access
resources outside the server instance. When the external resource is
Windows, the user is authenticated as the Windows user specified in
the credential.

For Windows logins (ie. Integrated Security or SSPI connection string) the engine will always impersonate the caller before accessing outside resources. Which implies that the access to the shared folder will occur under the impersonated context of the original Windows login. Which in turns implies 'double-hop' delegation will occur and the authentication with the shared file will be subject to the domain policies on constraining delegation. And all domains that give 1 cent on security will have constrained delegation restriction in place and will require you to set up the SQL Server service account to be enabled for constrained delegation.

StackExchange Database Administrators Q#30091, answer score: 8