SQL Server 2005 MSDB Access

  • Thread starter Thread starter cappjr
  • Start date Start date
C

cappjr

On my SQL Server, I have a stored proc called 'sp_exec_job' that
simply starts a job:

EXEC [msdb].[dbo].sp_start.job @JOB_NAME

When I run this from Management Studio, it works fine. Yea.

But, when I try to execute sp_exec_job from my .NET (C#) code (on the
same server as the SQL DB), I get the following error:

System.Data.SqlClient.SqlException: Could not find server 'msdb' in
sysservers. Execute sp_addlinkedserver to add the server to
sysservers.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)

Any ideas?
 
Sorry, the above message is not correct (I pasted the wrong message).
This is the error I am getting...

System.Data.SqlClient.SqlException: EXECUTE permission denied on
object 'sp_start_job', database 'msdb', schema 'dbo'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
 
Sorry, the above message is not correct (I pasted the wrong message).
This is the error I am getting...

System.Data.SqlClient.SqlException: EXECUTE permission denied on
object 'sp_start_job', database 'msdb', schema 'dbo'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObje­ct
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
Click to expand...

Never mind, i just gave my SQL user owner permissions on the msdb
database. Duh. Sorry to waste anyone's time.
 
Also, do not name your own stored procedure like "sp_xxxxxxx", which is used
for system stored procedures.

Sorry, the above message is not correct (I pasted the wrong message).
This is the error I am getting...

System.Data.SqlClient.SqlException: EXECUTE permission denied on
object 'sp_start_job', database 'msdb', schema 'dbo'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObje­ct
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
Click to expand...

Never mind, i just gave my SQL user owner permissions on the msdb
database. Duh. Sorry to waste anyone's time.
 
Back
Top