Problem while creating a job

Question

Post reply

Problem while creating a job

sri76

Say Hey Kid

Points: 666
More actions
May 15, 2009 at 12:28 am

#134195

Hi,
I am facing problem after creating a database and catalog and while creating a job for the catalog. The error message is as below
"the execute permission was denied on the object 'sp_add_job', database 'msdb', schema 'dbo'"
The process followed is
1. Created a new login using the below commands
USE master
GO
CREATE LOGIN NewUser WITH PASSWORD = 'NewUser@123'
GO
EXEC master..sp_addsrvrolemember @loginame = N'NewUser', @rolename = N'dbcreator'
GO
Note: It is not allowed to give the sysadmin server role for the login i create.
2. By using above process i was able to create database and catalog, but after this i need to create a job using below statements
USE msdb
GO
sp_add_job @job_name=' + dbName + '_Job'
GO
sp_add_jobserver @job_name = ' + dbName + '_Job'
GO
If i run the step 2 i get the mentioned error message.
Please tell me whether do i need to add any other permissions. If i give grant execute permission it is working. Is there any other way?
Thanks in advance
Sri

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

To create a SQL Server Agent job, a USER need to have execution permissions on objects

1. Execute sp_add_job to create a job.

2. Execute sp_add_jobstep to create one or more job steps.

3. Execute sp_add_jobserver to set the server for the job.

To Grant EXECUTION Permissions:

USE msdb

GO

CREATE USER NewUser FOR LOGIN NewUser

GO

GRANT EXECUTE ON OBJECT::dbo.sp_add_job TO NewUser

GO

GRANT EXECUTE ON OBJECT::dbo.sp_add_jobstep TO NewUser

GO

GRANT EXECUTE ON OBJECT::dbo.sp_add_jobserver TO NewUser

GO

--Ramesh

sri76 Say Hey Kid Points: 666 More actions · Answer 2

Thank you Ramesh for your reply.

It is working if i create the user for the login in msdb, but my problem is i have some 10 to 15 databases with separate username for each database.

Is it recommended to create those many users in msdb for the databases i have in my server. This count will increase further. Please suggest.

Sri

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

I don't see any problem in creating N users in any database, actually it is a better practice to grant only the permissions to users which they require and not any more than that. Granting excessive permissions will lead to severe security concerns.

Another option is to grant execute permissions to "public" database role but the issue is that it will give execute permissions to all logins in sql server irrespective of whether the user has granted permissions or not.

GRANT EXECUTE ON OBJECT::dbo.sp_add_job TO public

GO

GRANT EXECUTE ON OBJECT::dbo.sp_add_jobstep TO public

GO

GRANT EXECUTE ON OBJECT::dbo.sp_add_jobserver TO public

GO

[/code]

--Ramesh

sri76 Say Hey Kid Points: 666 More actions · Answer 4

Hi,

When i am execute the commands i get the following error

Msg 102, Level 15, State 1, Line 0

Incorrect syntax near 'msdb'.

Msg 102, Level 15, State 1, Line 0

Incorrect syntax near 'msdb'.

Msg 102, Level 15, State 1, Line 0

Incorrect syntax near 'msdb'.

sri76 Say Hey Kid Points: 666 More actions · Answer 5

Thank you Ramesh,

I changed the scope qalifier '::' to '.' and executed from msdb and worked fine. Like below

USE msdb

GO

GRANT EXECUTE ON msdb.dbo.sp_add_job TO public

GO

GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO public

GO

Now i need to test my application whether job is running as per schedule or not.

Thank you once again