Hi All,
Need help on sql permissions.
For example) demologin is my login. I want to give a permission to demologin so that he can change owner of any SQL Agent Job. So, is there a specific permission to accomplish this?
Thanks,
Sam
December 18, 2019 at 10:03 am
Sam
Have a read about SQL Server Agent Fixed Database Roles - I think that's what you need here.
John
December 18, 2019 at 4:20 pm
Hi All,
I want to give a permission to demologin so that he can change owner of any SQL Agent Job. So, is there a specific permission to accomplish this?
Sam
The pertinent part of your question is "so that he can change owner of any SQL Agent Job". The only way to do this is to grant demologin sysadmin permission. None of the SQL Server Agent Fixed Database Roles allow changing ownership of a job. Changes can only be made to jobs that are owned by the login (see: https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-ver15). In each of the roles, in the tables in the linked article, notice that it shows "owned jobs only".
I wouldn't recommend giving sysadmin permission to a login unless it is for an administrator of SQL Server. It is the highest permission level in SQL Server and there's literally nothing that a login with sysadmin permission can't do.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 18, 2019 at 4:42 pm
Good point. I don't know whether this would work, but you could try creating a stored procedure that calls sp_update_job with the @owner_login_name parameter, and give your login permission to run that.
John
I have a similar issue and am looking into using a stored procedure that can change job owners to the requestee and back
Useful reading: http://sommarskog.se/grantperm.html
December 18, 2019 at 6:35 pm
Good point. I don't know whether this would work, but you could try creating a stored procedure that calls sp_update_job with the @owner_login_name parameter, and give your login permission to run that.
John
Something like this could work, you'd probably have to sign the stored procedure with a certificate and grant the ability that way:
December 19, 2019 at 6:11 am
Thank you all for the help. I know that we need to give sysadmin server role. But was looking if there is any specific permissions or possible workarounds to accomplish it. Thanks again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply