none
Required Database Permissions for a SQL Server Management Agent for Export RRS feed

  • Question

  • Well, I think the title tells most of the story.

    I have a SQL Server MA that needs to write some information back to a table.  That information is used to process and generate additional data (into the same table) that is later imported and flowed through the other MAs. 

    When I run the export, I'm getting a "permission-issue" reported back from the MA.  I ran a SQL profiler trace on the database and captured the calls.  It's connection as the management agent account, and that account has select, insert, update and delete rights to that table.  The only error in the trace occurs immediately after the insert statement and is "Cursor is not open".

    I made the MA account a db_owner of the database; same error.  There's no way I'd get sysadmin rights to fly, so I didn't even try it.

    What am I missing?  I can't find the permissions requirement for the sp_opencursor statement.  But, that's for another forum.

    Thanks,

    Greg

    Friday, June 24, 2016 7:42 PM

All replies

  • Greg,

    Are you using a view or a table? Please double check.

    Db_Owner role should be sufficient. But if you are trying to write to a view there are additional restrictions on how the view can be setup.

    Thanks,

    David


    David Lundell, Get your copy of FIM Best Practices Volume 1 http://blog.ilmbestpractices.com/2010/08/book-is-here-fim-best-practices-volume.html

    Friday, June 24, 2016 9:01 PM
  • David,

    It's a table. 

    db_owner doesn't fix it.

    I wonder if the account needs public rights to the master database.  That would strike me as odd, as I don't need that for the MAs I have that just read data from the database.  I'm investigating the cursor stored procedures and the rights required to run those.

    Saturday, June 25, 2016 1:13 PM
  • Double check the default database for that SQL login is set to something it does have permissions for.

    David Lundell, Get your copy of FIM Best Practices Volume 1 http://blog.ilmbestpractices.com/2010/08/book-is-here-fim-best-practices-volume.html

    Monday, June 27, 2016 4:23 PM
  • Well, that didn't work. Neither did giving it public rights to the master and msdb databases. 

    Just for grins, I have the management agent account and the sync service account sysadmin to the db server.  Same error. So, there's something else going on.  The error messages are useless.

    Greg

    Monday, June 27, 2016 8:54 PM
  • I did a little more digging, the message the MA is returning is "cursor is not open".  Working on it.....
    Monday, June 27, 2016 9:25 PM
  • I found it.  I'm an idiot.  I was a code problem in a trigger for the destination table.
    Monday, June 27, 2016 9:37 PM