dmoServer.JobServer.Jobs.Count launches xp_servic

• dirkvv

  SSC Journeyman

  Points: 79

  More actions

  July 5, 2001 at 8:04 am

  #77717

  Problem description :

  I created a user in SQLserver who has no access on the stored procedure xp_servicecontrol.

  In my vb project I want to generate a list of all defined jobs but at the moment retrieve dmoServer.JobServer.Jobs.Count

  i get an error "permission

  denied on xp_servicecontrol....". Can someone tell me why the count

  statement will call a procedure to start/stop sqlservices ?

  Problem is that the customer doesn't want to give permission to that stored

  procedure.

  I tried to get a list of available jobs using sp_help_job but a user who is

  not a member of the sysadmin fixed role can use sp_help_job to view only the

  jobs he/she owns and we need to view all jobs.

  Any help will be appreciated.

  Public dmoServer As SQLDMO.SQLServer

  Public Function Retrieve_Dataload(ByVal Server As String, _

  ByVal User As String, _

  ByVal Pwd As String) As ADODB.Recordset

  Const csPROC_NAME = ".Retrieve_Dataload"

  On Error GoTo ErrHnd

  ' OpenResource DDconnStr

  Set Retrieve_Dataload = New ADODB.Recordset

  Dim lcnt As Long

  Retrieve_Dataload.CursorLocation = adUseClient

  'Retrieve.CursorType = adOpenForwardOnly

  Retrieve_Dataload.Fields.Append "NAME", adVarChar, 255

  Retrieve_Dataload.Fields.Append "DESCRIPTION", adVarChar, 255

  Retrieve_Dataload.Open

  'Connect to the sqlserver

  '

  If dmoServer Is Nothing Then Set dmoServer = New SQLDMO.SQLServer

  dmoServer.Connect Server, User, Pwd

  For lcnt = 1 To dmoServer.JobServer.Jobs.Count

  Retrieve_Dataload.AddNew

  Retrieve_Dataload.Fields("NAME").Value =

  dmoServer.JobServer.Jobs(lcnt).Name

  Retrieve_Dataload.Fields("DESCRIPTION").Value =

  dmoServer.JobServer.Jobs(lcnt).Description

  Retrieve_Dataload.MoveFirst

  Next

  ExitHnd:

  If Not dmoServer Is Nothing Then If dmoServer.Status = SQLDMOSvc_Running

  Then dmoServer.DisConnect

  Set dmoServer = Nothing

  Exit Function

  ErrHnd:

  ''If Not dmoServer Is Nothing Then If dmoServer.status =

  SQLDMOSvc_Running Then dmoServer.DisConnect

  Set dmoServer = Nothing

  MsgBox Err.Description

  Resume ExitHnd

  End Function

  Private Sub Command1_Click()

  Dim rs As ADODB.Recordset

  Set rs = Retrieve_Dataload(TxtServer.Text, txtuser.Text, txtpwd.Text)

  End Sub

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  July 5, 2001 at 8:32 am

  #416926

  A quick workaround be to grant the user select permissions on sysjobs in msdb, count using a select with ADO.

  Andy

  Andy
  SQLAndy - My Blog!
  Connect with me on LinkedIn
  Follow me on Twitter