Are you connecting using a SQL authentication login or a Windows login? If a SQL auth login, how are you giving that SQL login "full control permissions" to a folder in Windows? Windows has no idea about any SQL authentication logins you've created in SQL Server. Please show us exactly what you mean by "I created a user on the server" - what user? what server? SQL Server or Windows?
As a workaround, you could also create a stored procedure that executes as sa
or a Windows login that is part of the sysadmin group, and give this lesser-privileged user the ability to execute. However I was able to backup a database by adding a peon user with no other permissions at all and simply adding them to the db_backupoperator
role:
CREATE LOGIN peon WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
GO
CREATE DATABASE splunge;
GO
USE splunge;
GO
CREATE USER peon FROM LOGIN peon;
GO
EXEC sp_addrolemember 'db_backupoperator', 'peon';
GO
EXECUTE AS USER = 'peon';
GO
BACKUP DATABASE splunge
TO DISK = 'C:\tmp\splung.bak' -- change this path obviously
WITH INIT, COMPRESSION;
GO
REVERT;
GO
So, I would validate that the SQL Server service account has sufficient privileges to write to the path in question. I know you said that this was the case but as I've shown this doesn't seem to be a problem with the peon
user but rather the underlying engine's ability to write to the file system. If you try the above backup command without adding peon
to the db_backupoperator
role, you get this error (it doesn't let you get anywhere near the actual backup command or verify any permissions on the disk):
Msg 262, Level 14, State 1, Line 1
BACKUP DATABASE permission denied in database 'splunge'.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
If this is a Windows login, then please validate that the user does, in fact, have write permissions to the folder in question. Try a different folder other than the hierarchy under C:\Program Files\...
and don't try to write directly to the root (e.g. C:\file.bak
).