GRANTING A LOGIN RIGHTS TO SQL AGENTS

Question

Post reply

GRANTING A LOGIN RIGHTS TO SQL AGENTS

sahoong

SSCrazy

Points: 2545
More actions
July 20, 2009 at 3:52 am

#133095

The Gurus,
Could some please, tell me how to grant a login the right to SQL Agents? I mean, I want the login to be able to run jobs.
Many thanks in advance.
Regards,
Sahoong.

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

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

MADHAN_PERS SSC Enthusiast Points: 105 More actions · Answer 1

Hi here is the BOL Extracted content which answers your question.

<>

How to: Configure a User to Create and Manage SQL Server Agent Jobs (SQL Server Management Studio)

To configure a user to create or execute Microsoft SQL Server Agent jobs, you must first add an existing SQL Server login or msdb role to one of the following SQL Server Agent fixed database roles in the msdb database: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole.

By default, members of these database roles can create their own job steps that run as themselves. If these non-administrative users want to run jobs that execute other job step types (for example, SSIS packages), they will need to have access to a proxy account. All members of the sysadmin fixed server role have permission to create, modify, and delete proxy accounts.

<>

sahoong SSCrazy Points: 2545 More actions · Answer 2

Thanks so much Madhan_Pers,

I went to Book Online to get further information about how to carry out the instructions that you gave me and I got this

"In Object Explorer, expand a server.

Expand Security, and then expand Logins.

Right-click the login you wish to add to a SQL Server Agent fixed database role, and select Properties.

On the Database Access page of the Login Properties dialog box, select the row containing msdb.

Under Database roles for: msdb, check the appropriate SQL Server Agent fixed database role."

But there is nothing like Database Access Page on the Login Properties dialog box and so, I am unale to add the login to SQLAgentUserRole.

Could you please, tell me what other step or code that I could run to add the login to the SQLAgentUserRole?

Many thanks in advance.

Regards,

Sahoong.

Lowell SSC Guru Points: 323518 More actions · Answer 3

[ edit doh i think i misread your question; i thought this was related to backups over the network, getting xp_cmdshell to work over network, etc...sorry]

I don't think you can add login rights... I think you need to use an existing windows account or create a new windows user account, and use that for the security for SQL Agents instead.

As an example, I've created a Windows user on our domain named "SQLAdmin" with a password that never expires, gave it rights to a couple of server shares where backups will get copied to from the SQL Server, and made that account a local admin on the SQL Server.

then I went to services, and I use THAT account instead to run services.

Then, just like your BOL stuff states, I added that account to all the roles you mentioned.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sahoong SSCrazy Points: 2545 More actions · Answer 4