SSIS - Connecting to the Integration Services service on the computer "" failed with the following error: "Access is denied."

Problem :

Connecting to the Integration Services service on the computer "Server Name" failed with the following error: "Access is denied."

By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.

Resolution :

1. WIN + R --> Type  "dcomcnfg".

2.On the left-hand tree, navigate to Computers | My Computer | DCOM Config.

3.Find "Microsoft SQL Server Integration Services 12.0". This is for 2014; I believe earlier versions leave out "Microsoft," so check under "S" if you don't see it under "M".

4.Open Properties | Security and for each type of permission hit Edit and add an appropriate AD group or user. Be sure to check the specific permissions required, such as Remote Launch when editing Launch and Activation Permission. If anyone knows exactly which permissions are necessary for running SSIS, please share.

5.Launch Services, and restart the SQL Server Integration Services service.

You should now be able to connect to Integration Services from within SQL Studio or dtexec.exe

Script to find row count of all tables across multiple databases in SQL Server

We can achieve this by using undocumented stored procedure sp_msforeachdb.




Situations :


1. During DR Test


2. Any database level modifications or object level modifications due to huge load

SET NOCOUNT ON

DECLARE @query VARCHAR(4000) 
DECLARE @temp TABLE (
 DBName VARCHAR(200)
 ,TYPEName VARCHAR(10)
 ,TABLEName VARCHAR(300)
 ,COUNT INT
 )

SET @query = 'SELECT  ''?'',sysobjects.type, sysobjects.Name, sysindexes.Rows 
FROM   ?..sysobjects INNER JOIN ?..sysindexes ON sysobjects.id = sysindexes.id
WHERE  sysindexes.IndId < 2 order by sysobjects.Name'

INSERT @temp
EXEC sp_msforeachdb @query

SELECT *
FROM @temp
WHERE DBName <> 'tempdb'
ORDER BY DBName