List of all SSRS reports name and their datasource

SELECT
    DS.Name AS DatasourceName,
    C.Name AS DependentItemName,
    C.Path AS DependentItemPath
FROM
    ReportServer.dbo.Catalog AS C
        INNER JOIN
    ReportServer.dbo.Users AS CU
        ON C.CreatedByID = CU.UserID
        INNER JOIN
    ReportServer.dbo.Users AS MU
        ON C.ModifiedByID = MU.UserID
        LEFT OUTER JOIN
    ReportServer.dbo.SecData AS SD
        ON C.PolicyID = SD.PolicyID AND SD.AuthType = 1
        INNER JOIN
    ReportServer.dbo.DataSource AS DS
        ON C.ItemID = DS.ItemID
WHERE
    DS.Name IS NOT NULL
ORDER BY
    DS.Name;

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

SSIS Package batch export and import

1.

You can create batch export command by following sql.

select 'dtutil /SQL "' +[p].[name] + '" /ENCRYPT FILE;"F:\SSISPackages\'+[p].[name]+'.dtsx;1"'
from msdb.dbo.sysdtspackages90 [p]

Output command will be like this.

   dtutil /SQL "SSIS_Package_Name" /ENCRYPT FILE;"F:\SSISPackages\SSIS_Package_Name.dtsx;1"

Here 1 means, copy all the sensitive data with user key.
Here we are exporting SSIS packages from SQL server to F:\SSISPackages directory.

Run these batch commands in Souce SSIS server's command prompt.

Copy all the .dtsx file from source to destination server.

2.
Batch SSIS packages import commands can be generated by following sql

select 'dtutil /FILE "C:\Users\s5142805\Downloads\SSIS pkg\' +[p].[name] + '.dtsx" /COPY SQL;"/'+[p].[name]+'"'
from msdb.dbo.sysdtspackages90 [p]

Output will be like this.

dtutil /FILE "C:\Users\s5142805\Downloads\SSIS pkg\ABMCashOut - Import v2.dtsx" /COPY SQL;"/ABMCashOut"

Here we are importing SSIS packages from C:\Users\s5142805\Downloads\SSIS pkg\ to SQL server directory.

Run these batch commands in Destination SSIS server's command prompt.

Find active connections and kill

Find active connections

SELECT DB_NAME(dbid) as DBName, dbid,loginame as LoginName,CAST(SPID AS VARCHAR(4)) AS spid
FROM     sys.sysprocesses WHERE dbid>10
ORDER BY dbid           

You will find spid. Then kill the connection by spid. Do not kill yourself.

KILL 61

MSSQL: List all table size in a database

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN    
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.Name

MSSQL: Creating Link Database Server

You can create any link database server with any provider string.

EXEC master.dbo.sp_addlinkedserver

    @server = N'LZAMSMEXPRD\AMSMEX',

    @srvproduct=N'MSSQL',

    @provider=N'SQLNCLI',

    @provstr=N'PROVIDER=SQLOLEDB;SERVER=lzamsmexqat\amsmex,1433'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LZAMSMEXPRD\AMSMEX',

    @useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='P@ssword!'

Eariler version could have some issue with user having no sysadmin role. You can use the following one for that.

EXEC master.dbo.sp_addlinkedserver

    @server = N'CHRGBKP2NT',

    @srvproduct=N'MSSQL',

    @provider=N'SQLNCLI',

@datasrc='PSM-TESTSVR2'


EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CHRGBKP2NT',
    @useself=N'False',@locallogin=NULL,@rmtuser=N'jcolton',@rmtpassword='jcolton254'


For SQL Server 2012, use the following script.

EXEC master.dbo.sp_addlinkedserver @server = N'TOCGJTIM1PV\JTIME', @srvproduct=N'MSSQL', @provider=N'SQLNCLI', @datasrc=N'TOCFJTIM1UV\JTIME'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TOCGJTIM1PV\JTIME',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

List MSSQL Database size

SELECT
    DB_NAME(db.database_id) DatabaseName,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
    (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
    (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
    LEFT JOIN
   (
   SELECT database_id, SUM(size) RowSize FROM sys.master_files
   WHERE type = 0
   GROUP BY database_id, type
   ) mfrows
   ON mfrows.database_id = db.database_id
   LEFT JOIN
  (
  SELECT database_id, SUM(size) LogSize FROM sys.master_files
  WHERE type = 1
  GROUP BY database_id, type
   ) mflog
  ON mflog.database_id = db.database_id
  LEFT JOIN
  (
  SELECT database_id, SUM(size) StreamSize
  FROM sys.master_files
  WHERE type = 2
  GROUP BY database_id, type
  ) mfstream
  ON mfstream.database_id = db.database_id
  LEFT JOIN
  (
   SELECT database_id, SUM(size) TextIndexSize
   FROM sys.master_files
   WHERE type = 4
   GROUP BY database_id, type
   ) mftext
   ON mftext.database_id = db.database_id

       MSSQL: How to recovery Database from SUSPECT Mode

Database generally Database moves to SUSPECT State due to following Reasons

• Database is corrupted
• ldf file is corrupted
• Disk Hardware Failure or Lack of Disk Space
• SQL Server does not have sufficient memory to Start SQL Server

Step 1

Never detach a suspect database it end up without re attach .as it is a Suspect database we can’t
take Backup of it. take the database into EMERGENCY MODE

– Take the database into Emergency mode
ALTER DATABASE [Database Name] SET EMERGENCY
GO

Step 2

set the database into single user mode using
– Change into single user mode
ALTER DATABASE [Database Name] SET SINGLE_USER

Step 3

Run DBCC Checkdb with allow data loss, by using this command all the open transactions will
be deleted ,
– Then run DBCC CheckDB command. Warning! REPAIR_ALLOW_DATA_LOSS can cause
some data to be lost!.
DBCC CheckDB (<[Database Name]> , REPAIR_ALLOW_DATA_LOSS)

Step 4

when the database repair is done set the database in multi user mode using

– Change from multiuser

ALTER DATABASE dbName SET MULTI_USER

Altogether

ALTER DATABASE ReportServer SET EMERGENCY
ALTER DATABASE ReportServer SET SINGLE_USER
DBCC CheckDB (ReportServer , REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE ReportServer SET MULTI_USER