Solved

SQL DTS  import transfers 0 rows

Posted on 2013-05-23
2
334 Views
Last Modified: 2013-05-28
I have a dts import job that is launched by Dynamics GP VBA code, which calls a stored procedure.  Here is the code in the stored proc:

DECLARE @COMMAND varchar(1000)
SELECT @COMMAND = 'DTExec /sq ' + @DTSPKG + ' /ser MANZANITA\BINARY'

EXEC master..xp_cmdshell @COMMAND

@DTSPKG is the DTS package name and is passed as an input parameter.

A proxy account is in place to allow non-admin users to use the xp_cmdshell extended procedure.  All permissions have been given to the stored procedures, to xp_cmdshell and to the destination table.  The proxy account has the sysadmin role in msdb and in the production and DYNAMICS databases.

The non-admin users are mapped to the production database in the public and DYNGRP (Dynamics GP users) roles and in the msdb database, they are mapped to the following roles:

public
db_datareader
db_dbdatawriter
db_ssisadmin
db_ssisltduser
db_ssisoperator

In component services, in MsDtsServer, each of the non-admin users have the following permissions:

Launch and Activation:
    Local Launch
    Remote Launch
    Local Activation
    Remote Activation

Access Permissions
    Local Access
    Remote Access

This has been working for 2 years.  Two days ago, it stopped working properly.  What happens now is the stored procedure appears to successfully launch and the DTS package is executed.  But 0 rows are transferred.  No error is generated - it just doesn't bring any data into the destination table.

According to the client's IT department, no updates, upgrades or other processes have been done to the server which could account for this.

If I make the non-admin users SQL sysadmins, the process works and data is successfully imported.  But as soon as I return their roles to what they were before, the process transfers 0 roles.

I need to get back to where these non-admin users can launch the DTS packages without having the sysadmin role.
0
Comment
Question by:thoecherl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 39195542
There must be some errors in SQL Logs or Event viewer because there is definitely a permission/rights issue is you say that "If I make the non-admin users SQL sysadmins, the process works and data is successfully imported".
And speaking about that - where do you "make the non-admin users SQL sysadmins"?
Are all databases on that same SQL Server?
0
 

Author Closing Comment

by:thoecherl
ID: 39202205
I made the non-admin users sysadmins in SQL Management Studio | Security | Logins.  Yes, all db's are on the same SQL server.

I have since figured out the problem.  When we first setup the program - unknown to us - on the folder in the Windows file system where the source file was located, someone had given Full Control to the folder to the Everyone group.  None of us realized that and when the program worked, we didn't bother to check, security on the folder.

The day that my program stopped working, the System Administrator discovered that hole in the security and took away the Everyone group Full Control.  To resolve that problem I added the Windows account that had been used as the SQL proxy account to the folder and granted Read and Write permission.  That solved the problem.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question