Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I get the following error when I execute my package as a SQL server agent job.

It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive.

I don't even know how to begin fixing this error.

Where should I check first?

Date        a value of time
Log     Job History (MyJob)

Step ID     1
Server      PCTSQL004
Job Name        MyJob
Step Name       Job_1
Duration        00:00:00
Sql Severity        0
Sql Message ID      0
Operator Emailed        
Operator Net sent       
Operator Paged      
Retries Attempted       0

Message
Executed as user: CS\DmcSysManager. The process could not be created for step 1 of job 0x63BB5A86DB23F947866D2A806BE4CC6B (reason: A required privilege is not held by the client).  The step failed.
share|improve this question
2  
Has it ever run successfully? –  Shawn Melton Jun 20 '13 at 1:11
    
What privileges does CS\DmxSysManager have? –  mrdenny Jun 20 '13 at 2:12
    
@ShawnMelton - the job - no, never. ssis package inside visual studio - yes. –  gregnorm Jun 20 '13 at 6:28
    
@mrdenny - I am not sure how I can find out the privileges of CS\DmcSysManager has. Can you tell me how to get them ? I can tell you once i know. –  gregnorm Jun 20 '13 at 6:29
    
Within the Windows OS there are groups which can be used to assign privileges to accounts. What groups is that account a member of? What fixed server roles in SQL Server is it a member of? –  mrdenny Jun 20 '13 at 16:49

2 Answers 2

up vote 2 down vote accepted

It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive.

You have to save the package with EncryptAllWithPassword using a password.

enter image description here

Then schedule it using SQL Agent Job as below :

"DriveLetter\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /FILE "SSIS_Package_Location\bin\Package.dtsx" /DECRYPT "PasswordStrong007" /CONFIGFILE "Location_to_Config_file_If_any\Config.dtsConfig" /CHECKPOINTING OFF /REPORTING E

Now the user account that you use should have permissions on the database server instance.

You can check the permissions using below T-SQL :

SELECT [Login Type] = CASE sp.type
        WHEN 'u'
            THEN 'WINDOWS Login'
        WHEN 's'
            THEN 'SQL Login'
        WHEN 'g'
            THEN 'GRP'
        END
    ,convert(CHAR(45), sp.NAME) AS srvLogin
    ,convert(CHAR(45), sp2.NAME) AS srvRole
    ,convert(CHAR(25), dbp.NAME) AS dbUser
    ,convert(CHAR(25), dbp2.NAME) AS dbRole
FROM sys.server_principals AS sp
JOIN sys.database_principals AS dbp ON sp.sid = dbp.sid
JOIN sys.database_role_members AS dbrm ON dbp.principal_Id = dbrm.member_principal_Id
JOIN sys.database_principals AS dbp2 ON dbrm.role_principal_id = dbp2.principal_id
LEFT JOIN sys.server_role_members AS srm ON sp.principal_id = srm.member_principal_id
LEFT JOIN sys.server_principals AS sp2 ON srm.role_principal_id = sp2.principal_id
share|improve this answer

There are a set of 'special' privileges listed under the Security role of SSMS. One of those is BulkAdmin. While I don't root around in this kind of stuff much, the error above hints that the user account the SSIS package is running under needs either dbo privilege or membership in one of these roles. What database roles are assigned to CS\DmcSysManager?

share|improve this answer
    
How do i find out what roles are assigned to database roles are assigned to CS\DmcSysManager ? –  gregnorm Jun 20 '13 at 6:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.