Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gilbertr2412
Starting Member
4 Posts |
 Posted - 2011-10-12 : 11:37:16
|
I have a job that needs to query this table.SQL Agent is run by a domain account. This account is a local Administator and also a SQL Server sysadmin. Specific roles (RSExecRole, SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole)I can log onto the database server using this same domain account and query sysjobs without errors. However,when the job executes, permission errors result again.  I have deleted this Domain account and all permissions from SQL Server, and then re-entered the account and permissions, but this error continues.This is build 10.0.4000, 64bit, Enterprise edition.I am beyond lost on this, so any help would be GREATLY appreciated! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-12 : 12:00:47
|
| whats the permission error you're speaking of? It might be that job is executing succesfully but inside it there might step which is trying to access data from some shared path or something which this account is not able to access. so first check exactly whats the error message.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
gilbertr2412
Starting Member
4 Posts |
Posted - 2011-10-12 : 13:27:46
|
| SELECT @jobID = j.job_id FROM msdb.dbo.sysjobs j WHERE j.name = @jobNameThis is the query that is failing.A stored procedure calls another stored proc called "vspIsSQLAgentJobRunning", which passes in a job name.That stored proc runs the above query, which throws the permission error.This happens even if I just do an EXEC vspIsSQLAgentJobRunning @jobname='a value'I can, however, run the above query directly in management studio without a permissions error.However, if I am logged in as the domain account which SQL Agent is running, I can query msdb.dbo.sysjobs without any permission errors.Hope that helps! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-12 : 13:30:27
|
| do you have execute permission on vspIsSQLAgentJobRunning?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
gilbertr2412
Starting Member
4 Posts |
Posted - 2011-10-12 : 14:24:01
|
| yes. the error occurs within the vspIsSQLAgentJobRunning procedure, not in the execution of the procedure.Plus I can run this in debug mode, walk line by line and I only get an error when it tries to query msdb.dbo.sysjobs. |
|
|
|
gilbertr2412
Starting Member
4 Posts |
Posted - 2011-10-12 : 14:31:37
|
| Well, I solved this, but I am still a bit confused since on our development system, this proc works.The vspIsSQLAgentJobRunning had a "EXECUTE AS OWNER" clause.I removed that and now no errors are generated. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-12 : 14:36:50
|
| ok. thats the reason. it was trying to impersonate the access rights of OWNER and hence it was failing the permissions------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
No select on msdb.dbo.sysjobs