6

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.
5
  • 2
    Has it ever run successfully?
    – user507
    Jun 20, 2013 at 1:11
  • What privileges does CS\DmxSysManager have?
    – mrdenny
    Jun 20, 2013 at 2:12
  • @ShawnMelton - the job - no, never. ssis package inside visual studio - yes.
    – gregnorm
    Jun 20, 2013 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, 2013 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, 2013 at 16:49

2 Answers 2

2

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
0

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?

1
  • How do i find out what roles are assigned to database roles are assigned to CS\DmcSysManager ?
    – gregnorm
    Jun 20, 2013 at 6:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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