August 12, 2008 at 11:15 am
What role should be given to a login so that he can run a sql job.
If only admin can run jobs is there a way out so that a user can run a job without admin previlages.
August 12, 2008 at 11:54 am
One of the SQL Server Agent roles might be what you need. Check out BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/719ce56b-d6b2-414a-88a8-f43b725ebc79.htm
Greg
August 12, 2008 at 11:58 am
I understand only server admin can run the jobs but am looking for a way where a user can be given access only to a job which he needs to run.
August 12, 2008 at 12:04 pm
Members of SQLAgentUserRole and SQLAgentReaderRole can execute jobs they own. Members of SQLAgentOperatorRole can execute any job in the instance.
Greg
August 12, 2008 at 12:12 pm
But where do i find these roles, i dont see them in server roles of the instance.
SQLAgentUserRole and SQLAgentReaderRole
August 12, 2008 at 12:18 pm
I find that role in msdb database but how can i add a user to that role.
August 12, 2008 at 12:30 pm
This should work for you, of course enter the proper role you want after the 'EXEC sp_addrolemember' command.
USE [msdb]
GO
CREATE USER [MyUser] FOR LOGIN [MyUser]
GO
USE [msdb]
GO
EXEC sp_addrolemember ‘SQLAgentUserRole’, ‘MyUser’
GO
If the user already exsists then only use the:
USE [msdb]
GO
EXEC sp_addrolemember ‘SQLAgentUserRole’, ‘MyUser’
GO
August 12, 2008 at 3:50 pm
It also wouldn't hurt to read about security in BOL. You'll find help on things like adding a user to a database role and use of SQL Server Agent roles.
Greg
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply