SQL Script For List Of All Database Size Details

IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
    DROP TABLE #space

CREATE TABLE #space (
      database_id INT PRIMARY KEY
    , data_used_size DECIMAL(18,2)
    , log_used_size DECIMAL(18,2)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO #space (database_id, data_used_size, log_used_size)
    SELECT
          DB_ID()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

SELECT
      d.database_id
    , d.name
    , d.state_desc
    , d.recovery_model_desc
    , t.total_size
    , t.data_size
    , s.data_used_size
    , t.log_size
    , s.log_used_size
    , bu.full_last_date
    , bu.full_size
    , bu.log_last_date
    , bu.log_size
FROM (
    SELECT
          database_id
        , log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
    FROM sys.master_files
    GROUP BY database_id
) t
JOIN sys.databases d ON d.database_id = t.database_id
LEFT JOIN #space s ON d.database_id = s.database_id
LEFT JOIN (
    SELECT
          database_name
        , full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
        , full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
        , log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
        , log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
    FROM (
        SELECT
              s.database_name
            , s.[type]
            , s.backup_finish_date
            , backup_size =
                        CAST(CASE WHEN s.backup_size = s.compressed_backup_size
                                    THEN s.backup_size
                                    ELSE s.compressed_backup_size
                        END / 1048576.0 AS DECIMAL(18,2))
            , RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
        FROM msdb.dbo.backupset s
        WHERE s.[type] IN ('D', 'L')
    ) f
    WHERE f.RowNum = 1
    GROUP BY f.database_name
) bu ON d.name = bu.database_name
ORDER BY t.total_size DESC

SQL Server Sample or Demo Tables

CREATE TABLE EMPLOYEE
(
EMPNO NUMERIC(4) ,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATETIME,
SAL NUMERIC(7,2),
COMM NUMERIC(7,2),
DEPTNO NUMERIC(2)
);
INSERT INTO EMPLOYEE VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800, NULL, 20);
INSERT INTO EMPLOYEE VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMPLOYEE VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMPLOYEE VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMPLOYEE VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMPLOYEE VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMPLOYEE VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMPLOYEE VALUES(7788,'SCOTT','ANALYST',7566,'1982-12-09',3000,NULL,20);
INSERT INTO EMPLOYEE VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMPLOYEE VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMPLOYEE VALUES(7876,'ADAMS','CLERK',7788,'1983-01-12',1100, NULL,20);
INSERT INTO EMPLOYEE VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMPLOYEE VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMPLOYEE VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

CREATE TABLE DEPARTMENT
(
DEPTNO NUMERIC(2),
DNAME VARCHAR(14),
LOC VARCHAR(13)
);
INSERT INTO DEPARTMENT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPARTMENT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPARTMENT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPARTMENT VALUES (40,'OPERATIONS','BOSTON');

CREATE TABLE SALGRADE
(
GRADE NUMERIC,
LOSAL NUMERIC,
HISAL NUMERIC
);
INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

Script out the Logins, Server Role Assignments and Permissions

SET NOCOUNT ON
-- Scripting Out the Logins To Be Created
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
   CASE
WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = '
+ CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
ELSE ' FROM WINDOWS WITH'
END
   +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name <> ('sa');

-- Scripting Out the Role Membership to Be Added
SELECT
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- Server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
AND SL.name NOT LIKE '##%##'
AND SL.name NOT LIKE 'NT AUTHORITY%'
AND SL.name NOT LIKE 'NT SERVICE%'
AND SL.name <> ('sa');


-- Scripting out the Permissions to Be Granted
SELECT
CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION'
THEN SrvPerm.state_desc
ELSE 'GRANT'
END
    + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' +
CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION'
THEN ''
ELSE ' WITH GRANT OPTION'
END collate database_default AS [-- Server Level Permissions to Be Granted --]
FROM sys.server_permissions AS SrvPerm
JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id
WHERE   SP.type IN ( 'S', 'U', 'G' )
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name <> ('sa');

SET NOCOUNT OFF

List out SQL Server details

SELECT SERVERPROPERTY ('MachineName') AS MachineName,@@SERVICENAME AS InstanceName,
            SERVERPROPERTY ('Edition') AS Edition,
            SERVERPROPERTY('Collation') AS Collation,
            SERVERPROPERTY ('INSTANCEDEFAULTDATAPATH')   AS DefaultDataPath,
             SERVERPROPERTY ('INSTANCEDEFAULTLOGPATH') AS DefaultLogPath,
            SERVERPROPERTY ('PRODUCTVERSION') AS ProductVersion,
             SERVERPROPERTY ('BUILDCLRVERSION') AS BuildCLRVersion,
            SERVERPROPERTY ('PROCESSID') AS ProcessID,
             SERVERPROPERTY ('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,
   CASE When ServerProperty('EngineEdition')=1 Then 'Desktop'
When ServerProperty('EngineEdition')=2 Then 'Standard'
When ServerProperty('EngineEdition')=3 Then 'Enterprise'
When ServerProperty('EngineEdition')=4 Then 'Express'
When ServerProperty('EngineEdition')=5 Then 'SQL Azure'
             Else 'Unknown'
END AS EngineEdition,
   CASE When ServerProperty('IsClustered') =0 Then  'Non-Clustered'
             When ServerProperty('IsClustered') =1 Then  'Clustered'
              Else 'Unknown'
   END AS ClusterType,ServerProperty('ProductLevel') AS ProductLevel

How can I get the Login and the user mapped in all the databases?

The below script is used to find out all the user and login information.

DECLARE @cmd Varchar(8000)
SELECT @cmd = 'SELECT @@SERVERNAME AS MachineName,@@SERVICENAME AS InstanceName,''?'' as DBName,User_Name = dp.name,
   DP.Principal_id ,DP.Type_Desc AS User_Type_Desc,DP.Default_Schema_Name,
   SP.Name as Login_Name,SP.type_desc AS Login_Type_Desc,DP.Create_Date,DP.Modify_Date
FROM ?.sys.database_principals AS dp
INNER JOIN sys.server_principals AS sp ON dp.[sid] = sp.[sid]'

EXEC sp_MSforeachdb @cmd

To view the SSIS permission list

The below script to provides the all SSIS user permissions


SELECT  'Database role' as [Object_type], rdp.name as [Path], mdp.name as [User] ,
    null as Permission_type,   null as [Permission]
from   sys.database_role_members drm
inner join sys.database_principals rdp on drm.role_principal_id = rdp.principal_id
inner join sys.database_principals mdp on drm.member_principal_id = mdp.principal_id

UNION ALL

/* Folders */

SELECT 'Folder' as [Object_type], f.name as [Path], pri.name as [User], Permission_type,

    CASE WHEN [obj].permission_type=1 THEN 'Read'
WHEN [obj].permission_type=2 THEN 'Modify'
WHEN [obj].permission_type=3 THEN 'Execution'
WHEN [obj].permission_type=4 THEN 'Manage permissions'
WHEN [obj].permission_type=100 THEN 'Create Objects'
WHEN [obj].permission_type=102 THEN 'Modify Objects'
WHEN [obj].permission_type=103 THEN 'Execute Objects'
WHEN [obj].permission_type=101 THEN 'Read Objects'
WHEN [obj].permission_type=104 THEN 'Manage Object Permissions'
    END AS [Permission]

FROM  [internal].[object_permissions] AS obj
INNER JOIN  [sys].[database_principals] AS pri ON obj.[sid] = pri.[sid]
INNER JOIN  [internal].[folders] as f ON f.folder_id=obj.object_id

UNION ALL

/* Projects */

SELECT 'Project' as [Object_type],f.Name + '/' + p.name as [Path], pri.name as [User], Permission_type,
      CASE  WHEN [obj].permission_type=1 THEN 'Read'
WHEN [obj].permission_type=2 THEN 'Modify'
WHEN [obj].permission_type=3 THEN 'Execution'
WHEN [obj].permission_type=4 THEN 'Manage permissions'
WHEN [obj].permission_type=100 THEN 'Create Objects'
WHEN [obj].permission_type=102 THEN 'Modify Objects'
WHEN [obj].permission_type=103 THEN 'Execute Objects'
WHEN [obj].permission_type=101 THEN 'Read Objects'
WHEN [obj].permission_type=104 THEN 'Manage Object Permissions'
     END AS [Permission]
FROM [internal].[project_permissions] AS obj
INNER JOIN [sys].[database_principals] AS pri  ON obj.[sid] = pri.[sid]
INNER JOIN [internal].[projects] as p  ON p.project_id=obj.object_id
INNER JOIN [internal].[folders] as f   ON p.folder_id=f.folder_id

UNION ALL

/* Environments */

SELECT 'Environment' as [object_type], f.name + '/' + e.environment_name as [path],pri.name as [user],  permission_type,
    CASE WHEN [obj].permission_type=1 THEN 'Read'
     WHEN [obj].permission_type=2 THEN 'Modify'
WHEN [obj].permission_type=3 THEN 'Execution'
WHEN [obj].permission_type=4 THEN 'Manage permissions'
WHEN [obj].permission_type=100 THEN 'Create Objects'
WHEN [obj].permission_type=102 THEN 'Modify Objects'
WHEN [obj].permission_type=103 THEN 'Execute Objects'
WHEN [obj].permission_type=101 THEN 'Read Objects'
WHEN [obj].permission_type=104 THEN 'Manage Object Permissions'
END AS [Permission]
FROM   [internal].[project_permissions] AS obj
INNER JOIN [sys].[database_principals] AS pri ON obj.[sid] = pri.[sid]
INNER JOIN [internal].[environments] as e ON e.environment_id=obj.object_id
INNER JOIN [internal].[folders] as f  ON e.folder_id=f.folder_id

SSIS Catalog Permissions

--STEP 1:

USE [master]
GO
CREATE LOGIN [INDLA\indlaradmin] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [INDLA\indlaradmin]
GO
GRANT CONNECT SQL TO [INDLA\indlaradmin]
GO

USE [msdb]
GO
CREATE USER [INDLA\indlaradmin] FOR LOGIN [INDLA\indlaradmin]
GO
USE [msdb]
GO
ALTER USER [INDLA\indlaradmin] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [msdb]
GO
ALTER ROLE [db_ssisadmin] ADD MEMBER [INDLA\indlaradmin]
GO
USE [msdb]
GO
GRANT CONNECT TO [INDLA\indlaradmin]
GO

USE [SSISDB]
GO
CREATE USER [INDLA\indlaradmin] FOR LOGIN [INDLA\indlaradmin]
GO
USE [SSISDB]
GO
ALTER USER [INDLA\indlaradmin] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [SSISDB]
GO
ALTER ROLE [db_owner] ADD MEMBER [INDLA\indlaradmin]
GO
USE [SSISDB]
GO
ALTER ROLE [ssis_admin] ADD MEMBER [INDLA\indlaradmin]
GO
USE [SSISDB]
GO
ALTER ROLE [ssis_failover_monitoring_agent] ADD MEMBER [INDLA\indlaradmin]
GO
USE [SSISDB]
GO
ALTER ROLE [ssis_logreader] ADD MEMBER [INDLA\indlaradmin]
GO
GRANT CONNECT TO [INDLA\indlaradmin]
GO

----------------------------------------------------------------------------------------------
Step2: To Provide The Folder Level Permissions


--Step2.1: find out the principal_id by runnning the below script and replace principal_id in step2.2 script

USE SSISDB
GO
SELECT DP.name,DP.principal_id ,DP.type_desc,DP.default_schema_name,
   DP.create_date,DP.modify_date,SP.name as Login_name,SP.type_desc AS Login_type_desc
FROM sys.database_principals AS DP
LEFT JOIN sys.server_principals AS SP ON DP.sid=SP.sid

-- (OR)--
USE SSISDB
GO
SELECT name,principal_id FROM sys.database_principals where name='INDLA\indlaradmin'


--Step2.2:

EXEC [SSISDB].[catalog].[grant_permission] @object_type=1, @object_id=1, @principal_id=20, @permission_type=1
GO

EXEC [SSISDB].[catalog].[grant_permission] @object_type=1, @object_id=1, @principal_id=20, @permission_type=2
GO

EXEC [SSISDB].[catalog].[grant_permission] @object_type=1, @object_id=1, @principal_id=20, @permission_type=4
GO

EXEC [SSISDB].[catalog].[grant_permission] @object_type=1, @object_id=1, @principal_id=20, @permission_type=100
GO

EXEC [SSISDB].[catalog].[grant_permission] @object_type=1, @object_id=1, @principal_id=20, @permission_type=102
GO

EXEC [SSISDB].[catalog].[grant_permission] @object_type=1, @object_id=1, @principal_id=20, @permission_type=103
GO

EXEC [SSISDB].[catalog].[grant_permission] @object_type=1, @object_id=1, @principal_id=20, @permission_type=101
GO

EXEC [SSISDB].[catalog].[grant_permission] @object_type=1, @object_id=1, @principal_id=20, @permission_type=104
GO