Q
Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server permission to create jobs and schedules in SSIS

We're having a lot of trouble scheduling and editing SSIS jobs in SQL Server 2005. The SA has created a proxy account for us to create SSIS packages. We're able to create jobs and schedules, but they're not running. Also, once we create a job and a schedule, we're not able to edit them – all the options are grayed out except "View." It has something to do with permissions, but we don't know what. Our accounts are supposed to have the appropriate level of access but apparently they're still missing something. What are we doing wrong? Can you provide a guide for non-SA users on creating jobs and schedules?

We're having a lot of trouble scheduling and editing SSIS jobs in SQL Server 2005. The SA has created a proxy account for us to create SSIS packages. We're able to create jobs and schedules, but they're not running. Also, once we create a job and a schedule, we're not able to edit them – all the options are grayed out except "View". It's got something to do with permissions, but we don't know what. Our accounts are supposed to have the appropriate level of access but apparently they're still missing something. What are we doing wrong? Can you provide a guide for non-SA users on creating jobs and schedules? We can share it with our server SA so she can give us the proper permissions.
SQL Server 2005 introduces the following MSDB database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

Before SQL 2005, I've always had problems with non-sa users being able to see jobs they don't own. We couldn't even tell if someone else's job was running or not. Your DBA should be able to create a proxy account with one or more of these new SQLAgent roles enable. They are explained in detail in MSDN at this link.

The above link provides the exact permissions associated with these new roles.

This was last published in November 2006

Dig Deeper on Microsoft SQL Server 2005

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close