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

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