question

NickRyanNZ-4337 avatar image
0 Votes"
NickRyanNZ-4337 asked Cathyji-msft edited

Permission required for job step to read from msdb view

I have a package that reads from the msdb view sysmail_allitems. I have sysadmin rights and if I run the package, it succeeds and extracts the rows I require. The same package run from a job step using the SSIS proxy account also doesn't fail but extracts 0 rows. The proxy has db_datareader permission on the view.

The DBA assisting me changed the proxy account to sysadmin at which point the job step worked. He then changed it back to dbowner and it stopped working.

How can I permit the job step to read from that table without granting our ssis proxy sysadmin (which seems like overkill)?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @NickRyanNZ-4337,

You need to give the sysadmin permission to the proxy account.

Quote from MS document sysmail_allitems (Transact-SQL);

Permissions

Granted to sysadmin fixed server role and DatabaseMailUserRole database role. When executed by a member of the sysadmin fixed server role, this view shows all messages. All other users only see the messages that they submitted.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.






5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.