January 29, 2015 at 9:39 am
Recently, we performed an in-place upgrade of a SQL VM from 2008 to 2012 just to test it out. Everything seems to have gone off without a hitch, or so I thought. When I logged into SSIS2k12 through SSMS 2k12, I had no issues. But, non-dba's seemingly can connect to SSIS, but can't expand the MSDB folder. They receive the common "Execute permission was denied on the object 'sp_ssis_listfolders', database 'msdb', schema 'dbo'." I've read through all the whitepapers that walk you through adding accounts to the DCom group as well as adjusting the permissions of the "Microsoft SQL Server Integration Services 11.0" item in Component Services. I have also manually granted execute access to the sp_ssis_*** stored procedures in MSDB. I've also added the account to the db_ssisltduser Database role.
So, anybody have other recommendations?
I compare being a DBA to that of Smokey The Bear. Helping to prevent wildfires in your forest of servers and applications.
January 29, 2015 at 12:40 pm
SQL_Ninja (1/29/2015)
Recently, we performed an in-place upgrade of a SQL VM from 2008 to 2012 just to test it out. Everything seems to have gone off without a hitch, or so I thought. When I logged into SSIS2k12 through SSMS 2k12, I had no issues. But, non-dba's seemingly can connect to SSIS, but can't expand the MSDB folder. They receive the common "Execute permission was denied on the object 'sp_ssis_listfolders', database 'msdb', schema 'dbo'." I've read through all the whitepapers that walk you through adding accounts to the DCom group as well as adjusting the permissions of the "Microsoft SQL Server Integration Services 11.0" item in Component Services. I have also manually granted execute access to the sp_ssis_*** stored procedures in MSDB. I've also added the account to the db_ssisltduser Database role.So, anybody have other recommendations?
I added our developers to the db_ssisoperator role. This allows developers to execute packages but it's not a problem for us. SQL Server connections always use Windows Authentication so when the package runs it's running under the developer's account which doesn't have permission to read/write in Production. Connections that require username and password use configuration files which the developers don't have access to in Production. Also, developers don't have access to the Production SSIS servers so they can't get on the machine and run them. Packages run on the machine they are called from.
If you switch to using the SSIS Catalog (SSISDB) and Project Deployment Mode then you have slightly different hoops to jump through. 🙂
Does that help at all?
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
December 7, 2016 at 11:09 am
I am having the same issue. I already try the same things that you already try and same as you no luck. Do you find the solution?
Thanks,
December 7, 2016 at 11:27 am
Actually, I found the solution for me.
In the sqlserversolutions.blogspot.mx website
The fifth recommendaton do the trick.
5. Find MsDtsSrvr.ini file at drive:\Program Files\Microsoft SQL Server\90\DTS\Binn (for 2008 find in 100\DTS\bin folder) and check for Server tag if it is <ServerName>.ServerName>
I hope this helps.
Later!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply