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\Filename.txt" could not be opened.
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]
EXEC sp_addrolemember N'db_datareader', N'XXXXX\yyyy'
USE [myDbName]
EXEC sp_addrolemember 'db_datawriter', 'XXXXX\yyyy'

Open in new window

but those commands result in error message 15410 User or role 'XXXXX\YYYY' does not exist in this database.

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?
LVL 52
Dale FyeOwner, Developing Solutions LLCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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 ......
Vitor MontalvãoMSSQL Senior EngineerCommented:
SQL Server service account needs to have permissions in that path (P:\Pathname\Subpathname\), so it can access the desired file.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCAuthor Commented:
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?
ste5anSenior DeveloperCommented:
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.
SQL is layered.
The service runs under certain credentials which are the basis under which the filesystem access is granted.
The rights granted within the DATAbase server are limited in scope within the DB. So an SQL sysadmin role can not grant access beyond what the sql agent has access to.

The issue often in these situation is that something along the path lacks authorization for the user under whose credentials SQL agent is running access to the preceding directory or the file within.

Commonly, mapped drives are generally inaccessible by SQL server ...

Since you control both side, create a folder within the path where the SQL server DBs are such as drop_stuff_here and have your process drop the file there,,,,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
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.
ste5anSenior DeveloperCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.