SQL Server security best practice

Security! This is the word comes in mind of every concerned person when it come...

Change the Collation Settings in MS SQL Server

This post will show you how to change the collation settings in MS SQL Server for specific database...

Resolve collation conflict

In this post I will show you how you can resolve collation conflict error...

Book: SQL Server 2008 High Availability

In this book I have tried to cover every single piece of information that might requires for installing and configuring SQL Server HA option like Clustering, Replication, Log Shipping and Database Mirroring...

Why to recompile Stored Procedure

Generally, we create views and stored procedures (proc here after) ...


Accessing SSIS on a Remote Server

In one of my recent assignment I was asked to help developers who were not able to work on SSIS on a remote computer, whenever they made an attempt to connect to SSIS on Remote server they were getting “Access is Denied” error. Researching bit on this I come across an article on MSDN which help me resolve this issue – this issue is all about a DCOM Permission. 
Here are the steps which helped me resolve this issue by granting necessary permission and adding developer user[s] or group for DCOM:
1) Open Component Services MMC Snap-in by Open Control Panel –> Administrative Tools –> Component Services
2) Expand Component Services node –> Expand Computers –> Expand My Computer and click DCOM Config
3) Select DCOM Config and it will display the sub list in the right pane, select MsDTSServer100
4) Right click on MsDTSServer100 and select Security tab
5) As shown in the above snapshot assign proper permission to user[s] or user group by clicking on Edit button under (you have to select Customize before you click Edit button)
a) Launch and Activation Permissions
     a.1) Local Launch
    a.2)  Remote Launch
    a.3) Local Activation
    a.4) Remote Activation
b) Access Permissions
    b.1) Local Access
   b.2) Remote Access
c) Configuration Permissions
   c.1)  Full Control
   c.2) Read
    c.3) Special Permission

6) Assign the appropriate permission on “My Computer” under Component Services
7) In MSDB database grant db_dtsoperator permission to user[s] or user group
8) Restart the Integration Services services, and you are done.
source : http://msdn.microsoft.com/en-us/library/aa337083.aspx