April 27, 2023 at 10:02 am
I have a requirement for specific users to view SQL Agent jobs through SSMS. They've been granted the SQLAgentOperatorRole and that works up to a point. However for jobs owned by another login, if the job step is a "SQL Server Integration Services Package" type, when clicking on the "Configuration" tab, the "Connection Managers" and "Advanced" tabs are disabled. The only way I can enable access to these tabs is to add the users to the "sysadmin" server role. This is excessive for my needs.
Is there a way that a user can view these sub-tabs without giving them the keys to the kingdom?
Thanks.
April 27, 2023 at 1:54 pm
Did you try TargetServersRole in msdb ? it's right next to SQLAgentUserRole
April 27, 2023 at 1:57 pm
Hi Alex. I hadn't tried it but I just have without success 🙁
April 29, 2023 at 2:44 pm
I haven't tested this but maybe it requires one of the SSIS permission roles? I think this is the relevant documentation https://learn.microsoft.com/en-us/sql/integration-services/security/integration-services-roles-ssis-service?view=sql-server-ver15
As I said not tested and some of those roles might also give excessive permissions. Just an idea that might help.
April 30, 2023 at 4:51 pm
Why do they need to see that information? It could contain sensitive data (e.g. passwords) that should not be exposed - and really doesn't provide any additional information that is useful when viewing the agent job. In fact, exposing that information could cause more confusion because the values displayed may not be the actual values being used because they are overridden by an environment variable.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 9, 2023 at 11:26 am
I haven't tested this but maybe it requires one of the SSIS permission roles? I think this is the relevant documentation https://learn.microsoft.com/en-us/sql/integration-services/security/integration-services-roles-ssis-service?view=sql-server-ver15
As I said not tested and some of those roles might also give excessive permissions. Just an idea that might help.
Thanks for the feedback. Unfortunately that didn't solve the problem either. I even went to the extreme of granting every role in the msdb and SSISDB databases (excluding "deny") without it making any difference to the results.
May 9, 2023 at 11:28 am
Why do they need to see that information? It could contain sensitive data (e.g. passwords) that should not be exposed - and really doesn't provide any additional information that is useful when viewing the agent job. In fact, exposing that information could cause more confusion because the values displayed may not be the actual values being used because they are overridden by an environment variable.
I have a select number of colleagues who need to see the configuration of a job as part of their role. Since I'm only presenting this information to selected colleagues, I see no issue with what they can see. At present, my only solution is to grant them "sysadmin" and that's *definitely* not a solution!
June 1, 2023 at 1:45 pm
check this link, try this in a test environment first:
https://en.dirceuresende.com/blog/sql-server-entendendo-as-permissoes-e-roles-do-sql-agent-sqlagentuserrole-sqlagentreaderrole-sqlagentoperatorrole/
=======================================================================
June 2, 2023 at 11:53 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply