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

Script to Check Database Properties

SELECT
 sysDB.database_id,
 sysDB.Name as 'Database Name',
 syslogin.Name as 'DB Owner',
 sysDB.state_desc,
 sysDB.recovery_model_desc,
 sysDB.collation_name,
 sysDB.user_access_desc,
 sysDB.compatibility_level,
 sysDB.is_read_only,
 sysDB.is_auto_close_on,
 sysDB.is_auto_shrink_on,
 sysDB.is_auto_create_stats_on,
 sysDB.is_auto_update_stats_on,
 sysDB.is_fulltext_enabled,
 sysDB.is_trustworthy_on
from sys.databases sysDB

INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid

SQL Script to check total and free disk space of physical drive/LUN/mount drive

declare @svrName varchar(255)

declare @sql varchar(400)

--by default it will take the current server name, we can the set the server name as well

set @svrName = @@SERVERNAME

set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

--creating a temporary table

CREATE TABLE #output

(line varchar(255))

--inserting disk name, total space and free space value in to temporary table

insert #output

EXEC xp_cmdshell @sql

--script to retrieve the values in MB from PS Script output

select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename

   ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,

   (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'

   ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,

   (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'

from #output

where line like '[A-Z][:]%'

order by drivename

--script to retrieve the values in GB from PS Script output

select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename

   ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,

   (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'

   ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,

   (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'

from #output

where line like '[A-Z][:]%'

order by drivename

--script to drop the temporary table

drop table #output

Run the below script on existing server to extract DB Users, Roles, Object Level Permissions.

SET NOCOUNT ON

SELECT ScriptDateTime,scripts AS 'Scripts'
FROM   (SELECT Getdate() AS ScriptDateTime,
'CREATE USER [' + DP.name + '] FOR LOGIN ['
+ SP.name + ']' + CASE WHEN DP.type_desc != 'WINDOWS_GROUP' THEN
' WITH DEFAULT_SCHEMA = ['+Isnull(DP.default_schema_name, 'dbo')+']'
--+ CHAR(13)+CHAR(10)+'GO'
ELSE ''--+ CHAR(13)+CHAR(10)+'GO'
END       AS Scripts
FROM   sys.database_principals DP, sys.server_principals SP
WHERE  SP.sid = DP.sid
AND DP.name NOT IN ( 'DBO', 'GUEST', 'INFORMATION_SCHEMA', 'SYS',
'PUBLIC', 'DB_OWNER', 'DB_ACCESSADMIN', 'DB_SECURITYADMIN',
'DB_DDLADMIN', 'DB_BACKUPOPERATOR', 'DB_DATAREADER'  ,
'DB_DATAWRITER', 'DB_DENYDATAREADER', 'DB_DENYDATAWRITER', 'DB_X' )
UNION

--Extracting Database Roles Permissions for the DB USers.

SELECT Getdate() AS ScriptDateTime,
'EXEC sp_addrolemember @rolename ='
+ Space(1)
+ Quotename(User_name(rm.role_principal_id), '''')
+ ', @membername =' + Space(1)
+ Quotename(User_name(rm.member_principal_id), '''')
          --+ CHAR(13)+CHAR(10)+'GO'
          AS '--Role Memberships'
FROM   sys.database_role_members AS rm
WHERE  User_name(rm.role_principal_id)
+ User_name(rm.member_principal_id) != 'DB_OWNERDBO'
--ORDER BY rm.role_principal_id ASC

UNION



--Extracting object level permissions

SELECT Getdate() AS ScriptDateTime,
CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END +
Space
(1) +
perm.permission_name + Space(1)
+ 'ON ' + Quotename(User_name(obj.schema_id))
+ '.' + Quotename(obj.name) + CASE WHEN cl.column_id IS NULL THEN Space(
0
) ELSE
'(' + Quotename(cl.name) + ')' END + Space(1) + 'TO'
+ Space(1)
+ Quotename(User_name(usr.principal_id)) COLLATE database_default + CASE
WHEN perm.state <> 'W' THEN Space(0)
ELSE Space(1) + 'WITH GRANT OPTION'
                                                                    END
          --+ CHAR(13)+CHAR(10)+'GO'
          AS '--Object Level Permissions'
FROM   sys.database_permissions AS perm
INNER JOIN sys.objects AS obj
        ON perm.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr
        ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl
       ON cl.column_id = perm.minor_id
          AND cl.[object_id] = perm.major_id
--ORDER BY perm.permission_name ASC, perm.state_desc ASC

UNION


--Extracting database level permissions


SELECT Getdate() AS ScriptDateTime,
CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END +
Space
(1) +
perm.permission_name + Space(1)
+ Space(1) + 'TO' + Space(1)
+ Quotename(User_name(usr.principal_id)) COLLATE database_default + CASE
WHEN perm.state <> 'W' THEN Space(0)
ELSE Space(1) + 'WITH GRANT OPTION'
                                                                    END
          --+ CHAR(13)+CHAR(10)+'GO'
          AS '--Database Level Permissions'
FROM   sys.database_permissions AS perm
INNER JOIN sys.database_principals AS usr
        ON perm.grantee_principal_id = usr.principal_id
WHERE  perm.major_id = 0
AND ( permission_name
      + User_name(usr.principal_id) != 'CONNECTDBO' )
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
) AS UserScripts
ORDER  BY scripts

SQL Server Migration Plan

SQL Server Migration Plan

The migration plan would be executed in 3 phases.

1. Pre-migration checks
2. Actual DB migration and setup (Isolation of the DB, migration of jobs & logins)
3. Post-migration consistency and connectivity checks.

 Pre-Migration phase:

Pre-Migration Checklist (Source Server):

The following constraints / features shall be checked / noted down:

1. Database sizes.
2. Data and Log file location.
3. Server and Database properties (Collation, Auto Stats, DB Owner, Recovery Model, Compatibility level, Trustworthy option etc)
4. Collect the information of dependent applications, make sure application services will be stopped during the database migration
5. Database logins, users and their permissions.
6. Dependent objects (SQL Agent Jobs and Linked Servers)
7. Maintenance plans.

Pre-Migration Checklist (Destination Server):

Analogous to the above checklist, we shall check / create the following:

1. Adequate Disk space on the server.
2. Correct destination folders are created.
3. SQL Server is correctly installed and configured as per requirement.
4. Connectivity to the application servers and linked servers.

Migration Phase:

 Steps to be performed on the Source Server

1. Isolate Source server from all application and linked servers.
2. The Database(s) from the source server are backed up with password to ensure secure movement of  the data.
3. Script out all Jobs, Linked Servers, Logins and Users.
4. The Databases may now be put into Read-Only mode if required.

Steps to be performed on the Destination Server

1. Transfer the backup to the desired location.
2. Restore the database ensuring that the data and log files are placed in the correct location.
3. Recreate the Logins and User. Resolve Orphan User issues.
4. Re-establish Linked Servers and check any FTP Locations that are to be accessed.
5. Recreate the Jobs and Maintenance plans
6. Perform consistency checks and update index stats.

 Post Migration Phase:

1. Point the application to the new DB server IP (Connection string etc to altered by the application support team)
2. Restart Network connections between all stake holding servers (Network Team)
3. Check the SQL Server Error Log and Windows Error logs for any failures.
4. Confirm application functionality with end users.

Schematic Diagram

Pre – Migration Phase