• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 52
  • Last Modified:

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]
GO
EXEC sp_addrolemember N'db_datareader', N'XXXXX\yyyy'
GO
USE [myDbName]
GO
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?
0
Dale Fye
Asked:
Dale Fye
  • 3
  • 2
  • 2
  • +1
1 Solution
 
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?
0
 
arnoldCommented:
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 ......
0
 
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.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Dale FyeAuthor 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?
0
 
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.
0
 
arnoldCommented:
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,,,,
0
 
Dale FyeAuthor 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.
0
 
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.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now