| Author |
Topic |
|
badkneecap
Starting Member
10 Posts |
 Posted - 2008-06-06 : 16:42:30
|
| I have an SQL job that runs a .vbs script. It has the following code:Cmd2.ActiveConnection = Conn2Cmd2.CommandText = "USE msdb EXEC sp_update_job @job_name = 'Email Download',@enabled = 0"Cmd2.CommandType = 1Cmd2.Execute()It's not disabling the job. If I execute in Query Analyzer, it works. I'm thinking it might be a permissions issue. Any suggestions?TIA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
badkneecap
Starting Member
10 Posts |
Posted - 2008-06-06 : 17:15:46
|
| I am not that good with SQL. I know just enough to get by. I wouldn't know where to begin. It's a .vbs file and I'm not sure how I can trace through it. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-06 : 17:21:53
|
| SQL Profiler is a SQL Server tool that allows you to trace SQL Server activity. If you have the client tools installed and didn't uncheck SQL Profiler, then you've got it on your machine. Open it up, connect to your server, add SP:Completed (in Stored Procedures collection) and Exception (in Errors and Warnings collection) to the trace, start the trace, run your vbs, stop the trace, check the trace for what happened.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
badkneecap
Starting Member
10 Posts |
Posted - 2008-06-06 : 19:48:40
|
| Well, I followed what you said and could not find anything in the trace.What should I be looking for? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
badkneecap
Starting Member
10 Posts |
Posted - 2008-06-10 : 20:59:26
|
| There are groups of exceptions that all show the same message:Exception Error: 208, Severity: 16, State: 0 Microsoft SQL Server Management Studio Administrator UTROPICM-SQL-47\Administrator |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
badkneecap
Starting Member
10 Posts |
Posted - 2008-06-11 : 12:37:24
|
| I tried again, and no luck.But I ran the script manually to see what would happen. I got an error message that might shed some light. I'm logged in as the administrator when I ran the script, I got:EXECUTE permission denied on object 'sp_update_job', database 'msdb', schema 'dbo'Not sure which permissions I need to update and how. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-11 : 12:47:22
|
It is simple to find out what permissions are needed. Just open up SQL Server Books Online, type in sp_update_job and head to the permissions section.From BOL:quote:
PermissionsBy default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:SQLAgentUserRole SQLAgentReaderRole SQLAgentOperatorRole For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users.
Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
badkneecap
Starting Member
10 Posts |
Posted - 2008-06-11 : 13:26:34
|
| That worked! The user I was loggin in as needed to be added to the right server role.Thanks again. |
|
|
|
|
Disabling Job using VBScript