dmoServer.JobServer.Jobs.Count launches xp_servic

  • dirkvv

    SSC Rookie

    Points: 49

    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: 119676

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

    Andy

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply