Why not create the stored procedures in a separate database, so as to ensure portability, and avoid changes to the ConfigMgr database? Then, simply grant the service account the following permissions:
1. db_datareader role (on the ConfigMgr database) 2. Execute (on stored procedures in the external database) Cheers, Trevor Sullivan Microsoft PowerShell MVP From: [email protected] [mailto:[email protected]] On Behalf Of Jason Wallace Sent: Monday, June 2, 2014 10:48 AM To: [email protected] Subject: RE: [mssms] Permissions to run SQL Stored Procedures to extract data from the Site Database? Yes, I also responded in kind elsewhere. Also (for info) pointed to using Excel directly on the database <http://blogs.technet.com/b/manageabilityguys/archive/2013/04/11/mms-2013-ud b-338-additional-content.aspx> http://blogs.technet.com/b/manageabilityguys/archive/2013/04/11/mms-2013-udb -338-additional-content.aspx was an excellent MMS session on this _____ Date: Mon, 2 Jun 2014 08:40:01 -0700 From: [email protected] <mailto:[email protected]> Subject: Re: [mssms] Permissions to run SQL Stored Procedures to extract data from the Site Database? To: [email protected] <mailto:[email protected]> This may be "devil's advocate"... but is this CM? and that database is the CM database? If so, what's the blocker to use SRS, creating a report with whatever it is you need to dump, and in SRS scheduling that to run and export the results in CSV to a share somewhere? (or email it to some poor admin) Sherry Kissinger On Monday, June 2, 2014 9:58 AM, Cliff Hobbs <[email protected] <mailto:[email protected]> > wrote: Hi all, I need to create some Scheduled Tasks on a remote server that will run some SQL Stored Procedures on the Site Database Server in order to dump some data out to *.CSV files. A Service Account to run these Scheduled Tasks has been created but I'm not overly sure what permissions are required on the SQL side to get this to work. Ideally I'd like to keep the permissions to a minimum (i.e. they can run the stored procedures against the relevant tables and that's it), rather than just granting blank admin access. Hoping someone has done this before and can advise me please to save me trying to reverse engineer this. Many thanks in advance. Kind regards, Cliff
