July 5, 2001 at 8:04 am
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
July 5, 2001 at 8:32 am
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply