Creating index - Error 229 (Permission denied)

I have been working with my DBA and have not yet found the issue.
I have my development database on a SQL Server 2005 server. I enabled
it for full-text catalogs and created a catalog with no issues
whatsoever. When I go to create the index, however, I run through the
wizard (using SMS) until the last step. Upon finishing the wizard, I
get the following error:
Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
Additional Information:
A exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
EXECUTE permission denied on object 'sp_help_category', database
'msdb', schema 'dbo'.
SELECT permission denied on object 'sysjobs_view', database 'msdb',
schema 'dbo'. (Microsoft SQL Server, Error: 229).
I am the dbowner, and the DBA granted me temporary DBA privileges with
no difference (still received the same error). The DBA created the
index for me with no problems. I can see the index (I have not yet
tried using it). I can pull up the property pages and see "General"
and "Columns" views fine, but when I click on "Schedules", I receive
the same error as above.
Any thoughts?
Thanks.
You will need to be in the dbo_role on the msdb database as well.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<tlchurch@.gmail.com> wrote in message
news:1170424716.096179.254400@.l53g2000cwa.googlegr oups.com...
>I have been working with my DBA and have not yet found the issue.
> I have my development database on a SQL Server 2005 server. I enabled
> it for full-text catalogs and created a catalog with no issues
> whatsoever. When I go to create the index, however, I run through the
> wizard (using SMS) until the last step. Upon finishing the wizard, I
> get the following error:
> Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
> Additional Information:
> A exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> EXECUTE permission denied on object 'sp_help_category', database
> 'msdb', schema 'dbo'.
> SELECT permission denied on object 'sysjobs_view', database 'msdb',
> schema 'dbo'. (Microsoft SQL Server, Error: 229).
> I am the dbowner, and the DBA granted me temporary DBA privileges with
> no difference (still received the same error). The DBA created the
> index for me with no problems. I can see the index (I have not yet
> tried using it). I can pull up the property pages and see "General"
> and "Columns" views fine, but when I click on "Schedules", I receive
> the same error as above.
> Any thoughts?
> Thanks.
>
|||Hello tlchurch,
I am surprised your DBA hasn't ben able to help, as the error is fairly self
explanatory.
You do not have permissions to access sysjobs_view in database msdb and also
execute sp_help_category in msdb.
Your DBA needs to give you the correct permissions to see the schedules,
which are store in msdb.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> I have been working with my DBA and have not yet found the issue.
> I have my development database on a SQL Server 2005 server. I enabled
> it for full-text catalogs and created a catalog with no issues
> whatsoever. When I go to create the index, however, I run through the
> wizard (using SMS) until the last step. Upon finishing the wizard, I
> get the following error:
> Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
> Additional Information:
> A exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> EXECUTE permission denied on object 'sp_help_category', database
> 'msdb', schema 'dbo'.
> SELECT permission denied on object 'sysjobs_view', database 'msdb',
> schema 'dbo'. (Microsoft SQL Server, Error: 229).
> I am the dbowner, and the DBA granted me temporary DBA privileges with
> no difference (still received the same error). The DBA created the
> index for me with no problems. I can see the index (I have not yet
> tried using it). I can pull up the property pages and see "General"
> and "Columns" views fine, but when I click on "Schedules", I receive
> the same error as above.
> Any thoughts?
> Thanks.
>