Granting a access to SSIS server

To grant access to the Integration Services service

1. Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.
2. In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node.
3. Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.
4. On the Security tab, click Edit in the Launch and Activation Permissions area.
5. Add users and assign appropriate permissions, and then click Ok.
6. Repeat steps 4 - 5 for Access Permissions.
7. Restart SQL Server Management Studio.
8. Restart the Integration Services Service.
           a) On the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.
   
          b) In the SQL Server Configuration Manager snap-in, locate SQL Server Integration Services in the list of services, right-click SQL Server Integration Services, and then click Restart. 
9. Account might require sysdba role, if the following error shows up.
   "The Execute permission was denied on the object 'sp_ssis_listfolders'.

List all user permission to all databases

DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)

INSERT @DB_USers
EXEC sp_MSforeachdb

'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT

dbname,username ,logintype ,create_date ,modify_date ,

STUFF(

(

SELECT ',' + CONVERT(VARCHAR(500),associatedrole)

FROM @DB_USers user2

WHERE

user1.DBName=user2.DBName AND user1.UserName=user2.UserName

FOR XML PATH('')

)

,1,1,'') AS Permissions_user

FROM @DB_USers user1

GROUP BY

dbname,username ,logintype ,create_date ,modify_date

ORDER BY DBName,username