Last month i have been faced with a great question that was, how to give permission to any user for manage SQL Server Agent, create,edit and monitor jobs only?
This permission or role is relevant to server and service level, So we have to give permission at System Database Level and for SQL Server Agent only one Database is relating that is msdb.
So i follow the steps to give permission…
1.) Create a new user sql or domain : "SqlAgentUser" 2.) Now map the msdb database.
3.) Then give below permission form same box and finally click on ok a.Db_owner b.Db_ssisadmin c.Db_ssisltduser d.Db_ssisoperator e.Dc_proxy f.Dc_Operator g.Dc_Admin h.SQLAgentOperatorRole i.SQLAgentReaderRole j.SQLAgentUserRole
Now Connect to the Instance with the New User SqlAgentUser and credential. We will we have access to manage agent only non-other than db.
This permission or role is relevant to server and service level, So we have to give permission at System Database Level and for SQL Server Agent only one Database is relating that is msdb.
So i follow the steps to give permission…
1.) Create a new user sql or domain : "SqlAgentUser" 2.) Now map the msdb database.
3.) Then give below permission form same box and finally click on ok a.Db_owner b.Db_ssisadmin c.Db_ssisltduser d.Db_ssisoperator e.Dc_proxy f.Dc_Operator g.Dc_Admin h.SQLAgentOperatorRole i.SQLAgentReaderRole j.SQLAgentUserRole
Now Connect to the Instance with the New User SqlAgentUser and credential. We will we have access to manage agent only non-other than db.