Assigning permissions to a SQL Server database
SQL Server 2008
I recently created a new database on a clients development server and was trying to setup a job which would perform an insert daily at about 07:00. I created a T-SQL script and the upload ran successfully. I then migrated the script to an SP and ran that also ran without problem, but when I created the job and attempted to run the job manually, I get the following error:
Executed as user: XXXXX\yyyy.
Cannot bulk load because the file "P:\Pathname\Subpathname\F
Operating system error code 3(The system cannot find the path specified.).
[SQLSTATE 42000] (Error 4861). The step failed.
I dug a bit further into the properties of this new database and there are no users or roles displayed in the SSMS Database Properties - Permission window. When I tried to add the appropriate domain\user to this database, I received an "Object name could not be found" message in a popup dialog box, even though I'm currently logged in with that account.
When I go to the server logins in SSMS and select the account the I want to grant permissions to (my current login), it will not let me change the User displayed in the Users Mapped to this login at the top of the page, or change the roles that the account has to that particular database.
I've tried changing the roles for the database with:
This Domain\user account is listed in the server Security folder (it is what I am log in as) and is assigned sysadmin server role.
What am I doing wrong?
I recently created a new database on a clients development server and was trying to setup a job which would perform an insert daily at about 07:00. I created a T-SQL script and the upload ran successfully. I then migrated the script to an SP and ran that also ran without problem, but when I created the job and attempted to run the job manually, I get the following error:
Executed as user: XXXXX\yyyy.
Cannot bulk load because the file "P:\Pathname\Subpathname\F
Operating system error code 3(The system cannot find the path specified.).
[SQLSTATE 42000] (Error 4861). The step failed.
I dug a bit further into the properties of this new database and there are no users or roles displayed in the SSMS Database Properties - Permission window. When I tried to add the appropriate domain\user to this database, I received an "Object name could not be found" message in a popup dialog box, even though I'm currently logged in with that account.
When I go to the server logins in SSMS and select the account the I want to grant permissions to (my current login), it will not let me change the User displayed in the Users Mapped to this login at the top of the page, or change the roles that the account has to that particular database.
I've tried changing the roles for the database with:
USE [myDbName]
GO
EXEC sp_addrolemember N'db_datareader', N'XXXXX\yyyy'
GO
USE [myDbName]
GO
EXEC sp_addrolemember 'db_datawriter', 'XXXXX\yyyy'This Domain\user account is listed in the server Security folder (it is what I am log in as) and is assigned sysadmin server role.
What am I doing wrong?
ste5an
The problem here is normally that a job runs under a different account. This means that especially (mapped) network drives are not accessible. So what kind of "drive" is P? What file permissions (NTFS) are set for which accounts?
To ste5an's point, the SQL agent service based on your error lacks access rights to the file
The ntfs permission along the path, from p: all the way to the file ......
The ntfs permission along the path, from p: all the way to the file ......
SQL Server service account needs to have permissions in that path (P:\Pathname\Subpathname\)
ASKER
I'm out of my depth here, both with sysadmin and sqladmin.
Can the sysadmin assign permissions to the SQL Agent Service? Is that the right "user" to assign permissions to the path to or does it actually have another name?
Can the sysadmin assign permissions to the SQL Agent Service? Is that the right "user" to assign permissions to the path to or does it actually have another name?
Nope. The problem is the Windows account assigned to run SQL Agent jobs. This account needs the permissions to access this file.
See also Select an Account for the SQL Server Agent Service.
See also Select an Account for the SQL Server Agent Service.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all the input, but Arnold hit it on the head, pointing out:
"Commonly, mapped drives are generally inaccessible by SQL server ..."
As soon as I changed the file path to a UNC path it worked like a charm.
"Commonly, mapped drives are generally inaccessible by SQL server ..."
As soon as I changed the file path to a UNC path it worked like a charm.
Commonly, mapped drives are generally inaccessible by SQL server ...
This is for sure not correct.
The problem normally is: You create mapped drives only for normal user accounts. Then a mapped drive simply does not exist in the service accounts scope. When the mapped drive exists in that scope, then there is no difference between a mapped drive and an UNC path.
It's not common and not easy to create such a drive, but it is possible. Thus this not the reason.