Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL DTS  import transfers 0 rows

Posted on 2013-05-23
2
Medium Priority
?
346 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 40

Accepted Solution

by:
lcohan earned 1500 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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.
Screencast - Getting to Know the Pipeline
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 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