Archive for August, 2014

Allow non admin users to connect to SSIS remotely

August 26, 2014

When a user without sufficient rights attempts to connect to an instance of Integration Services on a remote server, the server responds with an “Access is denied” error message. You can avoid this error message by ensuring that users have the required DCOM permissions.

If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.
Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.

Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.
Select the DCOM Config node, and then select SQL Server Integration Services 11.0 in the list of applications that can be configured.

Right-click on SQL Server Integration Services 11.0 and select Properties.

In the SQL Server Integration Services 11.0 Properties dialog box, select the Security tab.
Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.

Click OK to close the dialog box.

Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.

Close the MMC snap-in.

Restart the Integration Services service.

What is filling up the transaction log?

August 14, 2014

SELECT a.transaction_id ,
DB_NAME(database_id) dbase_name,
database_transaction_begin_time ,
database_transaction_log_record_count ,
database_transaction_log_bytes_used ,
database_transaction_log_bytes_reserved ,
FROM sys.dm_tran_database_transactions a
LEFT JOIN sys.dm_tran_session_transactions b ON a.transaction_id = b.transaction_id
ORDER BY database_transaction_log_record_count DESC