Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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 ?

enter image description here

share|improve this question
2  
FYI photo.stackexchange.com/a/18756 – Remus Rusanu Dec 10 '12 at 13:12
@RemusRusanu it is from a paper not a board. :-) but thanks – Royi Namir Dec 12 '12 at 8:01

2 Answers

up vote 7 down vote accepted

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.

share|improve this answer
I need to tell the IT manager : "please grant access to ____" . Is three any select account which shows me the value of "______" ? – Royi Namir Dec 12 '12 at 8:00
The closest thing is Martin's suggestion to run whoami. But I suggest a different approach. Ask not what the account is - ask how can the account be set. Create a domain account for this purpose, grant access to this account, the create a credential in SQL for it and have the SQL login use this credential. – Remus Rusanu Dec 12 '12 at 9:45
up vote 5 down vote

It will be the SQL Server service account.

If xp_cmdshell is enabled (or you have permissions to temporarily enable it) you can use

EXEC master..xp_cmdshell 'whoami'
share|improve this answer
Thank you ( as always). – Royi Namir Dec 10 '12 at 12:03
will the 'whoami' result and the service account will always be the same ? – Royi Namir Dec 10 '12 at 12:04
@RoyiNamir - Yes AFAIK – Martin Smith Dec 10 '12 at 12:08
3  
This is not true for Windows logins, the call will be impersonated and subject to delegation. Is also incorrect for SQL logins that have set up a Windows credential. – Remus Rusanu Dec 10 '12 at 13:06
@RemusRusanu - Thanks for the clarification. Hopefully Royi will move the tick. – Martin Smith Dec 10 '12 at 13:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.