January 10, 2022 at 9:27 pm
I have a stored procedure that imports and processes data and at the end it is supposed to move the files to an archive file.
set @moveProcessed = 'Move "'+@filepath+@filename+'" \\TRINITYSQL\Archive\"'+SUBSTRING(@filename, 1, LEN(@filename) - 4)+'_'+cast(Format(getdate(), 'yyyyMMddHHmmss') as varchar)+'.csv"'
When i strip out the renaming stuff and just try to run it as a move command it works. I have read about making sure the user that runs sql server process is added to the file permissions but I cant seem to add the user. when I check services it shows the user is NT Service\MSSQLSERVER and for the agent it is NT Service\SQLSERVERAGENT. However, when I try to add that user it does not show them as a possible user.
So I read about making it a file share and did that and gave everyone rights to it but that did not work either.
Is there anyway I can check to see why it is causing the problem, some kind of error output?
January 10, 2022 at 10:42 pm
Update - After trying all kind of things I finally copied and pasted the full username NT Service\MSSQLSERVER and changed the location from the domain to the local computer and that then showed me two accounts and one of them was MSSQLSERVER. It would not work by typing in NT or MSSQL or SQL. I have to up the entire string of NT Service\MSSQLSERVER
I hope this saves another newb a some time.
January 11, 2022 at 4:21 pm
Personally, I'm not a huge fan of that "fix". My approach would be to change the SQL Server Service and SQL Agent Service to run as an AD account rather than as a local account. But that is just my opinion...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 11, 2022 at 7:06 pm
Personally, I'm not a huge fan of that "fix". My approach would be to change the SQL Server Service and SQL Agent Service to run as an AD account rather than as a local account. But that is just my opinion...
Better yet - use a gMSA account to run the services and create proxy accounts with minimal permissions.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 16, 2022 at 4:12 pm
Sorry for the late reply to this thread. Hey Jeffrey, when you say better yet a gMSA account to run the services and proxy accounts what are you meaning? I have two groups in active directory called SQL server reader and SQL server elevated and they are in their own OU. Are you adn brian saying to change the service ( in services) to run as a regular user - say RunSqlUser - and put my users in one of these two groups and only give those groups the permissions in sql server?
We are a small company with only a few users and I am new to most of this.
Thanks for any help.
March 22, 2022 at 6:41 am
Steps to fix access denied files
Greeting,
Rachel Gomez
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply