Solved

SQL DTS  import transfers 0 rows

Posted on 2013-05-23
2
324 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
2 Comments
 
LVL 39

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now