Can SQL Server have different access rights thans you will have in SSMS

SQL Server Administration
1

Hi,

I Have an SSIS package that gets data from a file and saves it on the a folder in C drive on the SQL Server.
Then in another task it rename and moves the file to a staging folder on the server. Finally, it copies the file to another folder on the and other server. bother the moving of the file and the copying is done running batch files trough process task.
This all runs great if I run it in SSIS on the server, or execute the package in SSMS on the server, or by running a sored procedure on the server.
However, if I run this in SSMS from my computer everything runs great except the last step of moving the file to the other server.

I think this is a rights issue of some kind. Like maybe SSMS on my machine does not have write access to the server. Is this possible, what can I do about it?

Thank you

2

short answer: yes. SQL Server can (usually does) have different access rights. When you run SSMS, it runs as an application on your machine under your account, which likely does not have write access to the server (that would be a "finding" if you are ever audited!) SQL Server itself usually runs under a service account with limited access to many things, but just enough to function. If the data files are installed on C: on the server, then it needs access to that drive. (well, only the directories that it uses, but some folks leave things a little more open)

3

AS to what do do: Make the file destination a package or project parameter. WHen you run it locally, set it to your C: drive or whatever you can write to. WHen it runs on the server, set the parameter to where the file should land in that case.

4

Thanks for the reply. However, I tried this and it did not work. I also found out the if I set a parameter on my local machine in SSMS, it changed it on the server as well, and this would not be good since there will be a few people doing this on different machines. But again it did not work anyway.
I think what I need to do is somehow be abele to set the access rights to be the same in the local machine(SSMS) as they are on the server to the file paths.
If you have any ideas on this please let me know.

Thank you

5

The default parameter is saved on the server, but you can change that when you run it. That's the idea behind parameterizing your packages. One set of parameters for test, another for production

Have you tried to run the server copy with parameters supplied at run time?