How to find out what user a SQL stored procedure accesses network shares as?

Question

I've been asked to find out why a program is failing. I've traced it to a SQL stored procedure. The program passes in the name of a file that the st. proc. presumably tries to read.

However, the SQL server is returning an error indicating that it cannot access the file because access is denied.

My connection string says Integrated Security=SSPI;. I know for sure that the account that I'm logged in with to run the program has access to the file.

How can I find out for sure what user account the SQL server is using to access the file?

Answer 1

The Windows Process Monitor is a good place to start. http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx

Out of interest what version of SQL, are we talking CLR or just plain ol' sprocs?

Answer 2

It will be using the SQL Server service account to access the file system.

Answer 3

You can look at this article. User USER_NAME to get the SQL Username for the logged in user.

However, it sounds like the problem might be permissions with the FILE, not with the DB. You'll want to check that the appropraite permissions are set on the File and Directory as well as on the DB.