Category: Database

-- turn on Windows cmd shell
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

-- use Windows command to map a drive
EXEC XP_CMDSHELL 'net use B: \\SMBSERVER\ShareName'
-- check the drive
EXEC XP_CMDSHELL 'Dir B:'

-- backup all databases, AD-HOC method, not recommended for daily backups
DECLARE @bk_path VARCHAR(256);
DECLARE @db_name VARCHAR(50);
DECLARE @bk_file_date VARCHAR(20);
DECLARE @bk_file_name VARCHAR(256);
DECLARE @i INT = 1;
 
/* Set backup path */
SET @bk_path = 'B:\Backup\';
 
/* Backup file format DBname_YYYYMMDD_HHMMSS.BAK. Change this as needed. */
SELECT @bk_file_date = FORMAT(GETDATE(), 'yyyyMMdd_hhmmss');
 
DECLARE @db_names TABLE (
   id INT IDENTITY(1,1) PRIMARY KEY, 
   db_name VARCHAR(50) NOT NULL );
 
INSERT INTO @db_names
   SELECT name 
   FROM master.sys.databases 
   WHERE name NOT IN ('master','model','msdb','tempdb')  /* Databases which needs to be excluded */
      AND state = 0 /* Include only the database which are online */
      AND is_in_standby = 0; /* database is not read only for log shipping */
 
WHILE EXISTS (SELECT 1 from @db_names WHERE Id = @i)
BEGIN
    
   SELECT @db_name = db_name from @db_names WHERE Id = @i;
   PRINT 'Backup Started: ' + @db_name
    
   SET @bk_file_name = @bk_path + @db_name + '_' + @bk_file_date + '.BAK';
 
   BACKUP DATABASE @db_name TO DISK = @bk_file_name WITH STATS;
 
   SET @i = @i + 1;
    
END
GO

-- delete a mapped drive after back has completed
EXEC XP_CMDSHELL 'net use B: /delete'

The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\130\COM' directory. System returned errorcode 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21100)
Get help: http://help/MSSQL_REPL21100

-- Get Recovery Model of All Databases:
SELECT Name,DATABASEPROPERTYEX(Name,'RECOVERY') AS [Recovery Model]
FROM master.dbo.sysdatabases

-- Sample Output
Name	Recovery Model
master	SIMPLE
tempdb	SIMPLE
model	SIMPLE
msdb	SIMPLE
TestDB1	FULL
TestDB2	SIMPLE

-- Check log sizes 
DBCC SQLPERF (LOGSPACE);
GO

-- Sample output
Database Name	Log Size (MB)	Log Space Used (%)	Status
master	2.242188	39.89547	0
tempdb	71.99219	20.26587	0
model	7.992188	17.64418	0
msdb	28.80469	5.994033	0
TestDB1	71.99219	5.225176	0
TestDB2	7.992188	29.86315	0

-- Get logical name of log file
SELECT name,physical_name FROM sys.master_files WHERE type_desc = 'LOG'

-- Shrink database log
USE [TestDB1]
GO
DBCC SHRINKFILE (N'TestDB1_LOG' , 0, TRUNCATEONLY)
GO

-- Shrink log by performing backup
BACKUP LOG [TestDB1] TO T:\TestDB1_log.bak

-- Set recovery model to SIMPLE and shrink log to 8GB
USE [TestDB1]
ALTER DATABASE [TestDB1] SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE ([TestDB1_log], 8192);
GO

-- Set recovery model to SIMPLE and shrink log to 1MB
USE TestDB1
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE TestDB1
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB
DBCC SHRINKFILE (TestDB1_log, 1);
GO
-- Optional: reset the database recovery model to FULL mode  
ALTER DATABASE TestDB1  
SET RECOVERY FULL;  
GO

-- Change recovery model to FULL
ALTER DATABASE [TestDB1] SET RECOVERY FULL
GO

-- Truncating Data File - NOT log file
USE TestDB1;  
GO  
SELECT file_id, name  -- obtain file_id prior to truncation
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY); 

-- Set all databases recovery model to SIMPLE and truncate all logs
declare @db_name nvarchar(124)
declare cursor cursor FOR
SELECT name AS DBName
FROM sys.databases
where name NOT IN ('tempdb','master','msdb','model)
ORDER BY Name;
OPEN cursor
FETCH NEXT FROM cursor INTO @db_name
    WHILE (@@FETCH_STATUS=0)
    BEGIN
        exec ('declare @logname nvarchar(124)
    USE [' + @db_name + ']
        SELECT @logname = name FROM sys.database_files where type = 1
        ALTER DATABASE ' + @db_name + ' SET RECOVERY SIMPLE
        DBCC SHRINKFILE (@logname , 0, TRUNCATEONLY)')
        ALTER DATABASE ' + @db_name + ' SET RECOVERY FULL
        FETCH NEXT FROM cursor INTO @db_name
    END
CLOSE cursor
DEALLOCATE cursor

USE master;  
GO  
ALTER DATABASE TEST_MSCRM SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE TEST_MSCRM MODIFY NAME = RENAMED_MSCRM;
GO  
ALTER DATABASE RENAMED_MSCRM SET MULTI_USER;
GO

Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
The database name 'RENAMED_MSCRM' has been set.

Completion time: 2012-10-29T16:09:11.0019350-07:00

-- Get sizes of multiple tables
use DATABASENAME
GO
IF OBJECT_ID('tempdb..#SpaceUsed') IS NOT NULL
	DROP TABLE #SpaceUsed

CREATE TABLE #SpaceUsed (
	 TableName sysname
	,NumRows BIGINT
	,ReservedSpace VARCHAR(50)
	,DataSpace VARCHAR(50)
	,IndexSize VARCHAR(50)
	,UnusedSpace VARCHAR(50)
	) 

DECLARE @str VARCHAR(500)
SET @str =  'exec sp_spaceused ''?'''
INSERT INTO #SpaceUsed 
EXEC sp_msforeachtable @command1=@str

SELECT TableName, NumRows, 
CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpaceMB,
CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpaceMB,
CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpaceMB,
CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpaceMB
FROM #SpaceUsed
-- WHERE TableName IN ('[dbo].[Table1]','[dbo].[Table2]')
ORDER BY TableName asc

use DATABASENAME
GO
SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    --SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    --SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    --(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB,
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 
    UnusedSpaceMB DESC, t.Name

# moveDbStorage.ps1
# Version 0.0.1
# This version is intended for a local MS SQL Server
# Requirements:
# - Integrationed authentication has been enabled within local SQL Server
# - Each database is expected to have been configured with 1 db file and 1 transaction logs file. Deviation to this will be handled in next script version.

$intendedDbLocation='D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA'
$intendedDbLogLocation='D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA'
# $intendedDbBackupLocation='Z:\Backups' # this iteration doesn't deal with backups
function moveDbStorage($databaseName,$intendedDbLocation,$intendedDbLogLocation,$intendedDbBackupLocation){
$erroractionpreference='stop'
    function confirmation($content,$testValue="I confirm",$maxAttempts=3){
        $confirmed=$false;
        $attempts=0;        
        $content|write-host
        write-host "Please review this content for accuracy.`r`n"
        while ($attempts -le $maxAttempts){
            if($attempts++ -ge $maxAttempts){
                write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                break;
                }
            $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
            if ($userInput.ToLower() -eq $testValue.ToLower()){
                $confirmed=$true;
                write-host "Confirmed!`r`n";
                break;                
            }elseif($userInput -like 'cancel'){
                write-host 'Cancel command received.'
                $confirmed=$false
                break
            }else{
                cls;
                $content|write-host
                write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                }
            }
        return $confirmed;
    }

    $results=[hashtable]@{}
    try{
        # Ensure that the server has SQL PowerShell tools 
        if(!(Get-Module -ListAvailable -Name 'sqlps' -ea SilentlyContinue)){
        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
        $nugetInstalled=Get-PackageProvider nuget
        if(!$nugetInstalled){Install-PackageProvider -Name Nuget -RequiredVersion 2.8.5.201 -Force}          
        $trustPSGallery=(Get-psrepository -Name 'PSGallery').InstallationPolicy
        If($trustPSGallery -ne 'Trusted'){
            Set-PSRepository -Name 'PSGallery' -InstallationPolicy Trusted
            }
        Install-Module PSWindowsUpdate -Confirm:$false -Force
        }         
        # Setting correct ACLs on provided storage locations
        $sqlInstance='MSSQLSERVER' # Assming default instance
        $sqlRunas=(Get-WMIObject Win32_Service -Filter "Name='$sqlInstance'").StartName
        foreach ($path in @($intendedDbLocation,$intendedDbLogLocation,$intendedDbBackupLocation)){
            write-host "Setting correct permissions on $path"
            if(!(Test-Path $path)){
                $null=New-Item -ItemType Directory -Force -Path $path
            }
            try{
                write-host "Granting $env:username and Administrators full access to $path..."
                $acl=Get-ACL $path
                $originalOwnerAccess=New-Object System.Security.AccessControl.FileSystemAccessRule($acl.Owner,"FullControl","Allow")
                $administratorsAccess=New-Object System.Security.AccessControl.FileSystemAccessRule('Administrators',"FullControl","Allow")
                #$thisUserAccess = New-Object System.Security.AccessControl.FileSystemAccessRule($env:username,"FullControl","Allow")
                $sqlAccess=New-Object System.Security.AccessControl.FileSystemAccessRule($sqlRunas,"FullControl","Allow")
                $acl.AddAccessRule($originalOwnerAccess)
                $acl.AddAccessRule($administratorsAccess)                
                #$acl.AddAccessRule($thisUserAccess)
                $sqlHasFullPermissions=$acl.Access|?{$_.IdentityReference -eq $sqlRunas -and $_.FileSystemRights -eq 'FullControl'}
                if(!$sqlHasFullPermissions){
                    $acl.AddAccessRule($sqlAccess)
                }                           
                Set-Acl $path $acl
            }catch{
                Write-warning $_
            }
        }

        # Get default locations    
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
        # $sqlServer=New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername 
        # $defaultBackupDirectory=$sqlServer.Settings.BackupDirectory
        # $defaultDataDirectory=$sqlServer.Settings.DefaultFile
        # $defaultLogDirectory=$sqlServer.Settings.DefaultLog
        # Change Default backup directory
        # if($defaultBackupDirectory -ne $intendedDbBackupLocation){
        #     write-host "Setting default backup location to $dbBackupLocation"
        #     $sqlServer.Properties["BackupDirectory"].Value = $dbBackupLocation
        #     $sqlServer.Alter()
        # }

        # Change database storage locations
        $databases=Get-SqlDatabase -ServerInstance $env:computername
        # $nonSystemDatabases=$databases|?{$_.Name -notin @('master','model','msdb','tempdb')}|sort -property Size
        $nonSystemDatabases=$databases|?{$_.IsSystemObject -ne $true}|sort -property Size
        Clear-Host
        $confirmedAll=confirmation 'RUN ALL CHANGES AUTOMATICALLY'
        
        foreach($database in $nonSystemDatabases){
            #$database=$nonSystemDatabases[0]
            $dbName=$database.Name
            $tsql="exec sp_helpdb @dbname='$dbName'"
            $dbProfile=Invoke-Sqlcmd -Query $tsql -ServerInstance $env:computername
            if($null -eq $dbProfile){
                write-warning "$dbName status is currently unknown"
                $results[$dbName]="$(get-date) : status unknown - no changes"
            }else{
                $filePaths=$dbProfile.GetEnumerator()|%{$_.filename}
                $dbFile=$filePaths[1]
                $dbLogFile=$filePaths[2]
                $intendedDbFile=Join-Path -Path $intendedDbLocation -ChildPath $($dbName+'.mdf')
                $intendedDbLogFile=Join-Path -Path $intendedDbLogLocation -ChildPath $($dbName+'_log.ldf')
                $moveDbStorage=$dbFile -ne $intendedDbFile
                $moveDbLogStorage=$dbLogFile -ne $intendedDbLogFile
                if($moveDbStorage -or $moveDbLogStorage){
                    # Alternative method:
                    # $logicalFileNames=Invoke-Sqlcmd -Query "
                    #     USE $dbName;
                    #     GO
                    #     SELECT file_id, name AS logical_name
                    #     FROM sys.database_files"
                    # $dbLogicalName=$logicalFileNames.logical_name[0]
                    # $dbLogLogicalName=$logicalFileNames.logical_name[0]
                    # $tSqlAlterInPlace="
                    #     Use MASTER
                    #     GO
                    #     ALTER DATABASE [$dbName] SET OFFLINE;  
                    #     GO
                    #     ALTER DATABASE [$dbName]
                    #         MODIFY FILE (NAME = '$dbLogicalName',   
                    #         FILENAME = '$intendedDbFile');  
                    #     GO
                    #     ALTER DATABASE [$dbName]   
                    #         MODIFY FILE ( NAME = '$dbLogLogicalName',   
                    #         FILENAME = '$intendedDbLogFile');  
                    #     GO
                    #     ALTER DATABASE AdventureWorks2014 SET ONLINE;  
                    #     GO"
                    # Invoke-Sqlcmd -Query $tSqlDetachDb

                    $confirmMessage="Set $dbName with these paths:`r`n$dbFile => $intendedDbFile`r`n$dbLogFile => $intendedDbLogFile"
                    $confirmed=if(!$confirmedAll){confirmation $confirmMessage}else{$true}
                    if($confirmed){
                        write-host "Detaching database $dbName..."
                        $timer=[System.Diagnostics.Stopwatch]::StartNew()
                        $startTime=get-date
                        $tSqlDetachDb="
                            Use MASTER
                            GO
                            ALTER DATABASE [$dbName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
                            GO
                            sp_detach_db @dbname = '$dbName', @skipchecks = 'true';
                            GO"
                        try{
                            Invoke-Sqlcmd -Query $tSqlDetachDb -EA Stop
                            if($moveDbStorage){
                                write-host "Moving $dbFile to $intendedDbFile..."
                                $null=move-item $dbFile $intendedDbFile
                            }
                            if($moveDbLogStorage){
                                write-host "Moving $dbLogFile to $intendedDbLogFile..."
                                $null=move-item $dbLogFile $intendedDbLogFile
                            }
                            $tSqlAttachDb="
                                Use MASTER
                                GO
                                sp_attach_DB '$dbName',
                                '$intendedDbFile',
                                '$intendedDbLogFile'
                                GO"
                            Invoke-Sqlcmd -Query $tSqlAttachDb
                            $elapsed=[math]::round($timer.Elapsed.TotalMinutes,2)
                            $endTime=get-date
                            $resultMessage="$dbName detached at $startTime and attached at $endTime - duration of $elapsed minutes."
                            write-host $resultMessage -foregroundcolor Green
                            $results[$dbName]="$endTime : $resultMessage"
                        }catch{
                            write-warning $_
                        }                          
                    }                    
                }else{
                    $results[$dbName]="$(get-date) : no changes"
                }
            }
        }
    }catch{
        Write-Warning $_
        }
    return $results
}

$results=moveDbStorage $databaseName $intendedDbLocation $intendedDbLogLocation $intendedDbBackupLocation
$results|write-host

-- Step 0: access the level of activities of all databases on SQL Server
WITH fs
AS
(
    select database_id, type, size * 8.0/1048576 size
    from sys.master_files
)
SELECT 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeGB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeGB  
FROM sys.databases db
ORDER BY LogFileSizeGB Desc

/*
name	        DataFileSizeGB	LogFileSizeGB
-------------   -------------   -------------
REPORTINGDB	    1.298828125	    1.713317871
DATABASE009	    235.080322265	1.407531738
DATABASE010	    6.718688964	    1.359436035
DATABASE999	    2.700012207	    0.869995117
*/

-- Step 1: get database size of a single database
USE REPORTINGDB;
SELECT
    name,
    size,
    size * 8.0/1048576 'Size (GB)',
    max_size
FROM sys.database_files;

/* Sample output:
name	            size	Size (GB)	max_size
------------        ------  ---------   ---------
REPORTINGDBDat.mdf	155680	1.187744140	-1
REPORTINGDBLog.ldf	33592	0.256286621	268435456
*/

-- Step 2: get database and transaction log file locations
USE REPORTINGDB;
SELECT
  name 'Logical Name', 
  physical_name 'File Location'
FROM sys.database_files;

/* Sample Output
Logical Name        File Location
------------        ------------
REPORTINGDBDat.mdf	D:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBDat.mdf
REPORTINGDBLog.ldf	D:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBLog.ldf
*/

-- Step 3: set database to single user mode and detach database
-- WARNING: this will render database as OFFLINE !!!
Use MASTER
GO
ALTER DATABASE REPORTINGDB -- Set database to single user mode
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO 
sp_detach_db @dbname = 'REPORTINGDB', @skipchecks = 'true'; -- Detach the database
GO

-- Step 4: Move the files to new locations using PowerShell
-- move-item $dbFile $intendedDbFile
-- move-item $dbLogFile $intendedDbLogFile

-- Step 5: re-attach database with new log file location
USE master
GO
sp_attach_DB 'REPORTINGDB',  -- Re-attach the database
'D:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBDat.mdf',
'E:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBLog.ldf'
GO

Activity ID: [ACTIVITYGUID]
Timestamp: Sun Nov 01 2020 05:14:05 (Pacific Standard Time)
Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: Caller [SYSTEMUSERID] does not have full permissions on the attribute '[FIELDNAME]' to unsecure the attributeDetail: 
<OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
  <ActivityId>[ACTIVITYGUID]</ActivityId>
  <ErrorCode>-2147158771</ErrorCode>
  <ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
  <Message>Caller [SYSTEMUSERID] does not have full permissions on the attribute [ACTIVITYGUID] to unsecure the attribute</Message>
  <Timestamp>2020-11-01</Timestamp>
  <ExceptionRetriable>false</ExceptionRetriable>
  <ExceptionSource i:nil="true" />
  <InnerFault i:nil="true" />
  <OriginalException i:nil="true" />
  <TraceText i:nil="true" />
</OrganizationServiceFault>

USE TEST_MSCRM
GO
declare @testOnlyDoNotCommit int = 1; -- Change this value to 0 to apply the fix
DECLARE @SysAdminProfileId uniqueidentifier = '572329C1-A042-4E22-BE47-367C6374EA45'
DECLARE @SecuredAttributeMissingInSysAdminProfile TABLE(AttributeName nvarchar(100), ObjectTypeCode int, AttributeDependencyNodeId uniqueidentifier, CanBeSecuredForCreate bit, CanBeSecuredForRead bit, CanBeSecuredForUpdate bit)
DECLARE @NonSecuredAttributeInSysAdminProfile TABLE(FieldPermissionId uniqueidentifier, AttributeName nvarchar(100), ObjectTypeCode int)

-- Identify field permissions missing in System Administrator profile for secured attributes.
INSERT INTO @SecuredAttributeMissingInSysAdminProfile (AttributeName, ObjectTypeCode, AttributeDependencyNodeId, CanBeSecuredForCreate, CanBeSecuredForRead, CanBeSecuredForUpdate )
SELECT a.LogicalName, e.ObjectTypeCode, dn.DependencyNodeId, a.CanBeSecuredForCreate, a.CanBeSecuredForRead, a.CanBeSecuredForUpdate 
  FROM AttributeLogicalView a INNER JOIN EntityLogicalView e ON a.EntityId = e.EntityId
    LEFT OUTER JOIN FieldPermission fp ON (fp.EntityName = e.ObjectTypeCode AND fp.AttributeLogicalName = a.LogicalName AND FieldSecurityProfileId = @SysAdminProfileId)
    LEFT JOIN DependencyNodeBase dn ON dn.ObjectId = a.AttributeId
 WHERE a.IsSecured = 1 AND fp.AttributeLogicalName IS NULL
 
-- Identify invalid field permissions defined in System Administrator profile for non-secured attributes.
INSERT INTO @NonSecuredAttributeInSysAdminProfile (FieldPermissionId, AttributeName, ObjectTypeCode)
SELECT fp.FieldPermissionId, fp.AttributeLogicalName, fp.EntityName
  FROM FieldPermission fp LEFT OUTER JOIN (
        SELECT a.LogicalName, e.ObjectTypeCode
          FROM  AttributeLogicalView a INNER JOIN EntityLogicalView e ON a.EntityId = e.EntityId
         WHERE a.IsSecured = 1
    ) ae ON (fp.EntityName = ae.ObjectTypeCode AND fp.AttributeLogicalName = ae.LogicalName)
 WHERE FieldSecurityProfileId = @SysAdminProfileId AND ae.LogicalName IS NULL

IF EXISTS(SELECT 1 FROM @SecuredAttributeMissingInSysAdminProfile UNION SELECT 1 FROM @NonSecuredAttributeInSysAdminProfile)
 BEGIN
    SELECT FORMATMESSAGE('@testOnlyDoNotCommit = %d', @testOnlyDoNotCommit)
    -- Output missing permissions.  
    SELECT 'Field permissions missing in System Administrator profile for secured attributes.'
    SELECT * FROM @SecuredAttributeMissingInSysAdminProfile 
    -- Output invalid permissions.
    SELECT 'Invalid field permissions defined in System Administrator profile for non-secured attributes.'
    SELECT * FROM @NonSecuredAttributeInSysAdminProfile
    BEGIN TRAN
     BEGIN TRY    
        DECLARE @BasicSolutionId uniqueidentifier = '25A01723-9F63-4449-A3E0-046CC23A2902'
        DECLARE @RecreatedFieldPermission TABLE(FieldPermissionId uniqueidentifier, AttributeLogicalName nvarchar(50), EntityName int, DependencyNodeId uniqueidentifier)
        -- Create missing System Administrator field permissions. 
        INSERT INTO FieldPermissionBase (FieldPermissionIdUnique, 
                         ComponentState,
                         SolutionId,
                         CanRead,
                         SupportingSolutionId,
                         CanCreate,
                         FieldSecurityProfileId,
                         EntityName,
                         FieldPermissionId,
                         IsManaged,
                         OverwriteTime,
                         CanUpdate,
                         AttributeLogicalName)
        OUTPUT inserted.FieldPermissionId, inserted.AttributeLogicalName, inserted.EntityName, NEWID() INTO @RecreatedFieldPermission
        SELECT NEWID(), 
             0,
             @BasicSolutionId,
             CASE WHEN a.CanBeSecuredForRead = 0 THEN 0 ELSE 4 END,
             NULL,
             CASE WHEN a.CanBeSecuredForCreate = 0 THEN 0 ELSE 4 END,
             @SysAdminProfileId,
             a.ObjectTypeCode,
             NEWID(),
             1,
             0,
             CASE WHEN a.CanBeSecuredForUpdate = 0 THEN 0 ELSE 4 END,
             a.AttributeName
        FROM @SecuredAttributeMissingInSysAdminProfile a
        SELECT * FROM @RecreatedFieldPermission
        -- Create dependency. 
        DECLARE @SystemAdminProfileDependencyNodeId uniqueidentifier
        SELECT @SystemAdminProfileDependencyNodeId = DependencyNodeId FROM DependencyNodeBase WHERE ObjectId = @SysAdminProfileId
        INSERT INTO DependencyNodeBase (BaseSolutionId, ObjectId, TopSolutionId, ParentId, IsSharedComponent, DependencyNodeId, ComponentType)
        OUTPUT inserted.*
        SELECT @BasicSolutionId, rfp.FieldPermissionId, @BasicSolutionId, '00000000-0000-0000-0000-000000000000', 0, rfp.DependencyNodeId, 71 
        FROM @RecreatedFieldPermission rfp
        INSERT INTO DependencyBase (DependentComponentNodeId, DependencyId, DependencyType, RequiredComponentNodeId)
        OUTPUT inserted.*
        SELECT rfp.DependencyNodeId, NEWID(), 1, a.AttributeDependencyNodeId
        FROM @RecreatedFieldPermission rfp JOIN @SecuredAttributeMissingInSysAdminProfile a ON rfp.EntityName = a.ObjectTypeCode AND rfp.AttributeLogicalName = a.AttributeName 
        WHERE a.AttributeDependencyNodeId IS NOT NULL
        INSERT INTO DependencyBase (DependentComponentNodeId, DependencyId, DependencyType, RequiredComponentNodeId)
        OUTPUT inserted.*
        SELECT rfp.DependencyNodeId, NEWID(), 1, @SystemAdminProfileDependencyNodeId
        FROM @RecreatedFieldPermission rfp
        WHERE @SystemAdminProfileDependencyNodeId IS NOT NULL
        -- Delete invalid System Administrator field permissions.
        DELETE FROM FieldPermissionBase
        OUTPUT deleted.*
        WHERE FieldPermissionId IN (SELECT FieldPermissionId FROM @NonSecuredAttributeInSysAdminProfile)
        -- Delete invalid dependencies.
        DELETE FROM DependencyBase
        OUTPUT deleted.*
        WHERE DependentComponentNodeId IN (
            SELECT DependencyNodeId FROM DependencyNodeBase WHERE ObjectId IN (SELECT FieldPermissionId FROM @NonSecuredAttributeInSysAdminProfile)
        )
        DELETE FROM DependencyNodeBase
        OUTPUT deleted.*
        WHERE ObjectId IN (SELECT FieldPermissionId FROM @NonSecuredAttributeInSysAdminProfile)
        IF (@testOnlyDoNotCommit = 1)
         BEGIN
            ROLLBACK TRAN
            SELECT 'TRANSACTION ROLLEDBACK'
         END
        ELSE
         BEGIN
            -- Recalculate PrincipalAttributeAccessMap for System Administrator profile members.
            DECLARE @principalUserIds EntityIdCollection
            DECLARE @principalTeamIds EntityIdCollection
            INSERT INTO @principalUserIds SELECT SystemUserId FROM SystemUserProfiles WHERE FieldSecurityProfileId = @SysAdminProfileId
            INSERT INTO @principalTeamIds SELECT TeamId from TeamProfiles WHERE FieldSecurityProfileId = @SysAdminProfileId
            EXEC p_PrincipalAttributeAccessMapReinitBulk @principalUserIds, @principalTeamIds, 1
            COMMIT TRAN 
            SELECT 'TRANSACTION COMMITED'
         END
     END TRY
     BEGIN CATCH
        SELECT 'ERROR while executing script. Transaction rolled back. ' + ERROR_MESSAGE()
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN
            SELECT 'TRANSACTION ROLLEDBACK'
        END
     END CATCH 
 END
ELSE
 BEGIN
    SELECT 'System administrator profile is healthy. Did not find any corruption.'
 END

USE [TEST_MSCRM]
GO
--DROP VIEW IF EXISTS [dbo].[FilteredContact];
--GO
--
-- report view for contact
--
CREATE view [dbo].[FilteredContact](
    [accountid],
    [accountiddsc],
    [accountidname],
    [accountidyominame],
    [accountrolecode],
    [accountrolecodename],
    [address1_addressid],
    [address1_addresstypecode],
    [address1_addresstypecodename],
    [address1_city] -- truncated for brevity  
) WITH view_metadata AS --WITH SCHEMABINDING AS
select
    [Contact].[AccountId],
    -- Truncated for brevity
    [Contact].[YomiMiddleName],
   dbo.fn_GetNumberFormatString(t.CurrencyPrecision, us.NumberGroupFormat, us.NegativeCurrencyFormatCode, 1, case o.CurrencyDisplayOption when 0 then t.CurrencySymbol when 1 then t.ISOCurrencyCode end, us.CurrencyFormatCode),
   dbo.fn_GetNumberFormatString(o.PricingDecimalPrecision, us.NumberGroupFormat, us.NegativeCurrencyFormatCode, 1, case o.CurrencyDisplayOption when 0 then t.CurrencySymbol when 1 then t.ISOCurrencyCode end, us.CurrencyFormatCode)
from Contact
    join SystemUserBase u on (u.SystemUserId = dbo.fn_FindUserGuid() and u.IsDisabled = 0)
    left join UserSettingsBase us on us.SystemUserId = u.SystemUserId
    left join OrganizationBase o on u.OrganizationId = o.OrganizationId
    left join TransactionCurrencyBase t on t.TransactionCurrencyId = [Contact].TransactionCurrencyId
    left outer join StringMap [AccountRoleCodePLTable] on 
		([AccountRoleCodePLTable].AttributeName = 'accountrolecode'
		and [AccountRoleCodePLTable].ObjectTypeCode = 2
		and [AccountRoleCodePLTable].AttributeValue = [Contact].[AccountRoleCode]
		and [AccountRoleCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address1_AddressTypeCodePLTable] on 
		([Address1_AddressTypeCodePLTable].AttributeName = 'address1_addresstypecode'
		and [Address1_AddressTypeCodePLTable].ObjectTypeCode = 2
		and [Address1_AddressTypeCodePLTable].AttributeValue = [Contact].[Address1_AddressTypeCode]
		and [Address1_AddressTypeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address1_FreightTermsCodePLTable] on 
		([Address1_FreightTermsCodePLTable].AttributeName = 'address1_freighttermscode'
		and [Address1_FreightTermsCodePLTable].ObjectTypeCode = 2
		and [Address1_FreightTermsCodePLTable].AttributeValue = [Contact].[Address1_FreightTermsCode]
		and [Address1_FreightTermsCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address1_ShippingMethodCodePLTable] on 
		([Address1_ShippingMethodCodePLTable].AttributeName = 'address1_shippingmethodcode'
		and [Address1_ShippingMethodCodePLTable].ObjectTypeCode = 2
		and [Address1_ShippingMethodCodePLTable].AttributeValue = [Contact].[Address1_ShippingMethodCode]
		and [Address1_ShippingMethodCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address2_AddressTypeCodePLTable] on 
		([Address2_AddressTypeCodePLTable].AttributeName = 'address2_addresstypecode'
		and [Address2_AddressTypeCodePLTable].ObjectTypeCode = 2
		and [Address2_AddressTypeCodePLTable].AttributeValue = [Contact].[Address2_AddressTypeCode]
		and [Address2_AddressTypeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address2_FreightTermsCodePLTable] on 
		([Address2_FreightTermsCodePLTable].AttributeName = 'address2_freighttermscode'
		and [Address2_FreightTermsCodePLTable].ObjectTypeCode = 2
		and [Address2_FreightTermsCodePLTable].AttributeValue = [Contact].[Address2_FreightTermsCode]
		and [Address2_FreightTermsCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address2_ShippingMethodCodePLTable] on 
		([Address2_ShippingMethodCodePLTable].AttributeName = 'address2_shippingmethodcode'
		and [Address2_ShippingMethodCodePLTable].ObjectTypeCode = 2
		and [Address2_ShippingMethodCodePLTable].AttributeValue = [Contact].[Address2_ShippingMethodCode]
		and [Address2_ShippingMethodCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address3_AddressTypeCodePLTable] on 
		([Address3_AddressTypeCodePLTable].AttributeName = 'address3_addresstypecode'
		and [Address3_AddressTypeCodePLTable].ObjectTypeCode = 2
		and [Address3_AddressTypeCodePLTable].AttributeValue = [Contact].[Address3_AddressTypeCode]
		and [Address3_AddressTypeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address3_FreightTermsCodePLTable] on 
		([Address3_FreightTermsCodePLTable].AttributeName = 'address3_freighttermscode'
		and [Address3_FreightTermsCodePLTable].ObjectTypeCode = 2
		and [Address3_FreightTermsCodePLTable].AttributeValue = [Contact].[Address3_FreightTermsCode]
		and [Address3_FreightTermsCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [Address3_ShippingMethodCodePLTable] on 
		([Address3_ShippingMethodCodePLTable].AttributeName = 'address3_shippingmethodcode'
		and [Address3_ShippingMethodCodePLTable].ObjectTypeCode = 2
		and [Address3_ShippingMethodCodePLTable].AttributeValue = [Contact].[Address3_ShippingMethodCode]
		and [Address3_ShippingMethodCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [CreditOnHoldPLTable] on 
		([CreditOnHoldPLTable].AttributeName = 'creditonhold'
		and [CreditOnHoldPLTable].ObjectTypeCode = 2
		and [CreditOnHoldPLTable].AttributeValue = [Contact].[CreditOnHold]
		and [CreditOnHoldPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [CustomerSizeCodePLTable] on 
		([CustomerSizeCodePLTable].AttributeName = 'customersizecode'
		and [CustomerSizeCodePLTable].ObjectTypeCode = 2
		and [CustomerSizeCodePLTable].AttributeValue = [Contact].[CustomerSizeCode]
		and [CustomerSizeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [CustomerTypeCodePLTable] on 
		([CustomerTypeCodePLTable].AttributeName = 'customertypecode'
		and [CustomerTypeCodePLTable].ObjectTypeCode = 2
		and [CustomerTypeCodePLTable].AttributeValue = [Contact].[CustomerTypeCode]
		and [CustomerTypeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotBulkEMailPLTable] on 
		([DoNotBulkEMailPLTable].AttributeName = 'donotbulkemail'
		and [DoNotBulkEMailPLTable].ObjectTypeCode = 2
		and [DoNotBulkEMailPLTable].AttributeValue = [Contact].[DoNotBulkEMail]
		and [DoNotBulkEMailPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotBulkPostalMailPLTable] on 
		([DoNotBulkPostalMailPLTable].AttributeName = 'donotbulkpostalmail'
		and [DoNotBulkPostalMailPLTable].ObjectTypeCode = 2
		and [DoNotBulkPostalMailPLTable].AttributeValue = [Contact].[DoNotBulkPostalMail]
		and [DoNotBulkPostalMailPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotEMailPLTable] on 
		([DoNotEMailPLTable].AttributeName = 'donotemail'
		and [DoNotEMailPLTable].ObjectTypeCode = 2
		and [DoNotEMailPLTable].AttributeValue = [Contact].[DoNotEMail]
		and [DoNotEMailPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotFaxPLTable] on 
		([DoNotFaxPLTable].AttributeName = 'donotfax'
		and [DoNotFaxPLTable].ObjectTypeCode = 2
		and [DoNotFaxPLTable].AttributeValue = [Contact].[DoNotFax]
		and [DoNotFaxPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotPhonePLTable] on 
		([DoNotPhonePLTable].AttributeName = 'donotphone'
		and [DoNotPhonePLTable].ObjectTypeCode = 2
		and [DoNotPhonePLTable].AttributeValue = [Contact].[DoNotPhone]
		and [DoNotPhonePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotPostalMailPLTable] on 
		([DoNotPostalMailPLTable].AttributeName = 'donotpostalmail'
		and [DoNotPostalMailPLTable].ObjectTypeCode = 2
		and [DoNotPostalMailPLTable].AttributeValue = [Contact].[DoNotPostalMail]
		and [DoNotPostalMailPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [DoNotSendMMPLTable] on 
		([DoNotSendMMPLTable].AttributeName = 'donotsendmm'
		and [DoNotSendMMPLTable].ObjectTypeCode = 2
		and [DoNotSendMMPLTable].AttributeValue = [Contact].[DoNotSendMM]
		and [DoNotSendMMPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [EducationCodePLTable] on 
		([EducationCodePLTable].AttributeName = 'educationcode'
		and [EducationCodePLTable].ObjectTypeCode = 2
		and [EducationCodePLTable].AttributeValue = [Contact].[EducationCode]
		and [EducationCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [FamilyStatusCodePLTable] on 
		([FamilyStatusCodePLTable].AttributeName = 'familystatuscode'
		and [FamilyStatusCodePLTable].ObjectTypeCode = 2
		and [FamilyStatusCodePLTable].AttributeValue = [Contact].[FamilyStatusCode]
		and [FamilyStatusCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [FollowEmailPLTable] on 
		([FollowEmailPLTable].AttributeName = 'followemail'
		and [FollowEmailPLTable].ObjectTypeCode = 2
		and [FollowEmailPLTable].AttributeValue = [Contact].[FollowEmail]
		and [FollowEmailPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [GenderCodePLTable] on 
		([GenderCodePLTable].AttributeName = 'gendercode'
		and [GenderCodePLTable].ObjectTypeCode = 2
		and [GenderCodePLTable].AttributeValue = [Contact].[GenderCode]
		and [GenderCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [HasChildrenCodePLTable] on 
		([HasChildrenCodePLTable].AttributeName = 'haschildrencode'
		and [HasChildrenCodePLTable].ObjectTypeCode = 2
		and [HasChildrenCodePLTable].AttributeValue = [Contact].[HasChildrenCode]
		and [HasChildrenCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [IsBackofficeCustomerPLTable] on 
		([IsBackofficeCustomerPLTable].AttributeName = 'isbackofficecustomer'
		and [IsBackofficeCustomerPLTable].ObjectTypeCode = 2
		and [IsBackofficeCustomerPLTable].AttributeValue = [Contact].[IsBackofficeCustomer]
		and [IsBackofficeCustomerPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [IsPrivatePLTable] on 
		([IsPrivatePLTable].AttributeName = 'isprivate'
		and [IsPrivatePLTable].ObjectTypeCode = 2
		and [IsPrivatePLTable].AttributeValue = [Contact].[IsPrivate]
		and [IsPrivatePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [LeadSourceCodePLTable] on 
		([LeadSourceCodePLTable].AttributeName = 'leadsourcecode'
		and [LeadSourceCodePLTable].ObjectTypeCode = 2
		and [LeadSourceCodePLTable].AttributeValue = [Contact].[LeadSourceCode]
		and [LeadSourceCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [MarketingOnlyPLTable] on 
		([MarketingOnlyPLTable].AttributeName = 'marketingonly'
		and [MarketingOnlyPLTable].ObjectTypeCode = 2
		and [MarketingOnlyPLTable].AttributeValue = [Contact].[MarketingOnly]
		and [MarketingOnlyPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [MergedPLTable] on 
		([MergedPLTable].AttributeName = 'merged'
		and [MergedPLTable].ObjectTypeCode = 2
		and [MergedPLTable].AttributeValue = [Contact].[Merged]
		and [MergedPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [msdyn_gdproptoutPLTable] on 
		([msdyn_gdproptoutPLTable].AttributeName = 'msdyn_gdproptout'
		and [msdyn_gdproptoutPLTable].ObjectTypeCode = 2
		and [msdyn_gdproptoutPLTable].AttributeValue = [Contact].[msdyn_gdproptout]
		and [msdyn_gdproptoutPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [ParticipatesInWorkflowPLTable] on 
		([ParticipatesInWorkflowPLTable].AttributeName = 'participatesinworkflow'
		and [ParticipatesInWorkflowPLTable].ObjectTypeCode = 2
		and [ParticipatesInWorkflowPLTable].AttributeValue = [Contact].[ParticipatesInWorkflow]
		and [ParticipatesInWorkflowPLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [PaymentTermsCodePLTable] on 
		([PaymentTermsCodePLTable].AttributeName = 'paymenttermscode'
		and [PaymentTermsCodePLTable].ObjectTypeCode = 2
		and [PaymentTermsCodePLTable].AttributeValue = [Contact].[PaymentTermsCode]
		and [PaymentTermsCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [PreferredAppointmentDayCodePLTable] on 
		([PreferredAppointmentDayCodePLTable].AttributeName = 'preferredappointmentdaycode'
		and [PreferredAppointmentDayCodePLTable].ObjectTypeCode = 2
		and [PreferredAppointmentDayCodePLTable].AttributeValue = [Contact].[PreferredAppointmentDayCode]
		and [PreferredAppointmentDayCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [PreferredAppointmentTimeCodePLTable] on 
		([PreferredAppointmentTimeCodePLTable].AttributeName = 'preferredappointmenttimecode'
		and [PreferredAppointmentTimeCodePLTable].ObjectTypeCode = 2
		and [PreferredAppointmentTimeCodePLTable].AttributeValue = [Contact].[PreferredAppointmentTimeCode]
		and [PreferredAppointmentTimeCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [PreferredContactMethodCodePLTable] on 
		([PreferredContactMethodCodePLTable].AttributeName = 'preferredcontactmethodcode'
		and [PreferredContactMethodCodePLTable].ObjectTypeCode = 2
		and [PreferredContactMethodCodePLTable].AttributeValue = [Contact].[PreferredContactMethodCode]
		and [PreferredContactMethodCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [ShippingMethodCodePLTable] on 
		([ShippingMethodCodePLTable].AttributeName = 'shippingmethodcode'
		and [ShippingMethodCodePLTable].ObjectTypeCode = 2
		and [ShippingMethodCodePLTable].AttributeValue = [Contact].[ShippingMethodCode]
		and [ShippingMethodCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [StateCodePLTable] on 
		([StateCodePLTable].AttributeName = 'statecode'
		and [StateCodePLTable].ObjectTypeCode = 2
		and [StateCodePLTable].AttributeValue = [Contact].[StateCode]
		and [StateCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [StatusCodePLTable] on 
		([StatusCodePLTable].AttributeName = 'statuscode'
		and [StatusCodePLTable].ObjectTypeCode = 2
		and [StatusCodePLTable].AttributeValue = [Contact].[StatusCode]
		and [StatusCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    left outer join StringMap [TerritoryCodePLTable] on 
		([TerritoryCodePLTable].AttributeName = 'territorycode'
		and [TerritoryCodePLTable].ObjectTypeCode = 2
		and [TerritoryCodePLTable].AttributeValue = [Contact].[TerritoryCode]
		and [TerritoryCodePLTable].LangId = 
			case us.UILanguageId 
				when 0 then o.LanguageCode
				else us.UILanguageId
			end)
    cross join dbo.fn_GetMaxPrivilegeDepthMask(2) pdm
where
(
	-- privilege check
	pdm.PrivilegeDepthMask is not null and
	(
	-- Owner check
	-- If the user has global access, then skip the ownership check
	((pdm.PrivilegeDepthMask & 0x8) != 0) or
	[Contact].OwnerId in 
		( -- returns only principals with Basic Read privilege for entity
			select pem.PrincipalId from PrincipalEntityMap pem WITH (NOLOCK)
			join SystemUserPrincipals sup WITH (NOLOCK) on pem.PrincipalId = sup.PrincipalId 
			where sup.SystemUserId = u.SystemUserId 
				and pem.ObjectTypeCode = 2
		)
		
	-- role based access
	or 
	
exists
(
	select 
	1
	where
	(
		-- deep/local security
		(((pdm.PrivilegeDepthMask & 0x4) != 0) or ((pdm.PrivilegeDepthMask & 0x2) != 0)) and 
		[Contact].[OwningBusinessUnit] in (select BusinessUnitId from SystemUserBusinessUnitEntityMap WITH (NOLOCK) where SystemUserId = u.SystemUserId and ObjectTypeCode = 2)
	) 
	or
	(
		-- global security
		((pdm.PrivilegeDepthMask & 0x8) != 0) and 
		[Contact].[OwningBusinessUnit] is not null 
	) 
)

	
	-- object shared to the user 
	or 
	[Contact].[ContactId] in 
		(
			select POA.ObjectId from PrincipalObjectAccess POA WITH (NOLOCK)
			join SystemUserPrincipals sup WITH (NOLOCK) on POA.PrincipalId = sup.PrincipalId
			where sup.SystemUserId = u.SystemUserId
				and POA.ObjectTypeCode = 2
				and ((POA.AccessRightsMask | POA.InheritedAccessRightsMask) & 1)=1
		)
	)
)

USE TEST_MSCRM
GO
--
-- base view for Contact
--
create view dbo.[Contact] (
    -- logical attributes
    [CreatedByYomiName],
    [CreatedByName],
    [OriginatingLeadIdYomiName],
    [OriginatingLeadIdName],
    [ModifiedByExternalPartyYomiName],
    [ModifiedByExternalPartyName],
    [CreatedByExternalPartyYomiName],
    [CreatedByExternalPartyName],
    [new_RetainedByYomiName],
    [new_RetainedByName],
    [DefaultPriceLevelIdName],
    [SLAName],
    [PreferredSystemUserIdName],
    [PreferredSystemUserIdYomiName],
    [TransactionCurrencyIdName],
    [PreferredServiceIdName],
    [SLAInvokedIdName],

    -- linked address entities
    [Address1_AddressTypeCode],
    [Address1_City],
    [Address1_Composite],
    [Address1_Country],
    [Address1_County],
    [Address1_AddressId],
    [Address1_Fax],
    [Address1_FreightTermsCode],
    [Address1_Latitude],
    [Address1_Line1],
    [Address1_Line2],
    [Address1_Line3],
    [Address1_Longitude],
    [Address1_Name],
    [Address1_PostalCode],
    [Address1_PostOfficeBox],
    [Address1_PrimaryContactName],
    [Address1_ShippingMethodCode],
    [Address1_StateOrProvince],
    [Address1_Telephone1],
    [Address1_Telephone2],
    [Address1_Telephone3],
    [Address1_UPSZone],
    [Address1_UTCOffset],

    [Address2_AddressTypeCode],
    [Address2_City],
    [Address2_Composite],
    [Address2_Country],
    [Address2_County],
    [Address2_AddressId],
    [Address2_Fax],
    [Address2_FreightTermsCode],
    [Address2_Latitude],
    [Address2_Line1],
    [Address2_Line2],
    [Address2_Line3],
    [Address2_Longitude],
    [Address2_Name],
    [Address2_PostalCode],
    [Address2_PostOfficeBox],
    [Address2_PrimaryContactName],
    [Address2_ShippingMethodCode],
    [Address2_StateOrProvince],
    [Address2_Telephone1],
    [Address2_Telephone2],
    [Address2_Telephone3],
    [Address2_UPSZone],
    [Address2_UTCOffset],

    [Address3_AddressTypeCode],
    [Address3_City],
    [Address3_Composite],
    [Address3_Country],
    [Address3_County],
    [Address3_AddressId],
    [Address3_Fax],
    [Address3_FreightTermsCode],
    [Address3_Latitude],
    [Address3_Line1],
    [Address3_Line2],
    [Address3_Line3],
    [Address3_Longitude],
    [Address3_Name],
    [Address3_PostalCode],
    [Address3_PostOfficeBox],
    [Address3_PrimaryContactName],
    [Address3_ShippingMethodCode],
    [Address3_StateOrProvince],
    [Address3_Telephone1],
    [Address3_Telephone2],
    [Address3_Telephone3],
    [Address3_UPSZone],
    [Address3_UTCOffset],

    -- ownership entries
    OwnerId,
    OwnerIdName,
    OwnerIdYomiName,
    OwnerIdDsc,
    OwnerIdType,
    OwningUser,
    OwningTeam,


	[AccountId],
	[AccountIdName],
	[AccountIdYomiName],
	[ParentContactId],
	[ParentContactIdName],
	[ParentContactIdYomiName],
    -- physical attributes
    [ContactId],
    [DefaultPriceLevelId],
    [CustomerSizeCode],
    [CustomerTypeCode],
    [PreferredContactMethodCode],
    [LeadSourceCode],
    [OriginatingLeadId],
    [OwningBusinessUnit],
    [PaymentTermsCode],
    [ShippingMethodCode],
    [ParticipatesInWorkflow],
    [IsBackofficeCustomer],
    [Salutation],
    [JobTitle],
    [FirstName],
    [Department],
    [NickName],
    [MiddleName],
    [LastName],
    [Suffix],
    [YomiFirstName],
    [FullName],
    [YomiMiddleName],
    [YomiLastName],
    [Anniversary],
    [BirthDate],
    [GovernmentId],
    [YomiFullName],
    [Description],
    [EmployeeId],
    [GenderCode],
    [AnnualIncome],
    [HasChildrenCode],
    [EducationCode],
    [WebSiteUrl],
    [FamilyStatusCode],
    [FtpSiteUrl],
    [EMailAddress1],
    [SpousesName],
    [AssistantName],
    [EMailAddress2],
    [AssistantPhone],
    [EMailAddress3],
    [DoNotPhone],
    [ManagerName],
    [ManagerPhone],
    [DoNotFax],
    [DoNotEMail],
    [DoNotPostalMail],
    [DoNotBulkEMail],
    [DoNotBulkPostalMail],
    [AccountRoleCode],
    [TerritoryCode],
    [IsPrivate],
    [CreditLimit],
    [CreatedOn],
    [CreditOnHold],
    [CreatedBy],
    [ModifiedOn],
    [ModifiedBy],
    [NumberOfChildren],
    [ChildrensNames],
    [VersionNumber],
    [MobilePhone],
    [Pager],
    [Telephone1],
    [Telephone2],
    [Telephone3],
    [Fax],
    [Aging30],
    [StateCode],
    [Aging60],
    [StatusCode],
    [Aging90],
    [PreferredSystemUserId],
    [PreferredServiceId],
    [MasterId],
    [PreferredAppointmentDayCode],
    [PreferredAppointmentTimeCode],
    [DoNotSendMM],
    [ParentCustomerId],
    [Merged],
    [ExternalUserIdentifier],
    [SubscriptionId],
    [PreferredEquipmentId],
    [LastUsedInCampaign],
    [ParentCustomerIdName],
    [ParentCustomerIdType],
    [TransactionCurrencyId],
    [OverriddenCreatedOn],
    [ExchangeRate],
    [ImportSequenceNumber],
    [TimeZoneRuleVersionNumber],
    [UTCConversionTimeZoneCode],
    [AnnualIncome_Base],
    [CreditLimit_Base],
    [Aging60_Base],
    [Aging90_Base],
    [Aging30_Base],
    [ParentCustomerIdYomiName],
    [CreatedOnBehalfBy],
    [ModifiedOnBehalfBy],
    [IsAutoCreate],
    [StageId],
    [ProcessId],
    [EntityImageId],
    [TraversedPath],
    [SLAId],
    [SLAInvokedId],
    [OnHoldTime],
    [LastOnHoldTime],
    [FollowEmail],
    [TimeSpentByMeOnEmailAndMeetings],
    [Business2],
    [Callback],
    [Company],
    [Home2],
    [CreatedByExternalParty],
    [ModifiedByExternalParty],
    [MarketingOnly],    
    [msdyn_gdproptout]
) with view_metadata as
select
    -- logical attributes
    [lk_contactbase_createdby].[YomiFullName],
    [lk_contactbase_createdby].[FullName],
    [contact_originating_lead].[YomiFullName],
    [contact_originating_lead].[FullName],    
    [sla_contact].[Name],

    -- linked address entities
    [XXaddress1].[AddressTypeCode],
    [XXaddress1].[City],
    [XXaddress1].[Composite],
    [XXaddress1].[Country],
    [XXaddress1].[County],
    [XXaddress1].[CustomerAddressId],
    [XXaddress1].[Fax],
    [XXaddress1].[FreightTermsCode],
    [XXaddress1].[Latitude],
    [XXaddress1].[Line1],
    [XXaddress1].[Line2],
    [XXaddress1].[Line3],
    [XXaddress1].[Longitude],
    [XXaddress1].[Name],
    [XXaddress1].[PostalCode],
    [XXaddress1].[PostOfficeBox],
    [XXaddress1].[PrimaryContactName],
    [XXaddress1].[ShippingMethodCode],
    [XXaddress1].[StateOrProvince],
    [XXaddress1].[Telephone1],
    [XXaddress1].[Telephone2],
    [XXaddress1].[Telephone3],
    [XXaddress1].[UPSZone],
    [XXaddress1].[UTCOffset],
    [XXaddress2].[AddressTypeCode],
    [XXaddress2].[City],
    [XXaddress2].[Composite],
    [XXaddress2].[Country],
    [XXaddress2].[County],
    [XXaddress2].[CustomerAddressId],
    [XXaddress2].[Fax],
    [XXaddress2].[FreightTermsCode],
    [XXaddress2].[Latitude],
    [XXaddress2].[Line1],
    [XXaddress2].[Line2],
    [XXaddress2].[Line3],
    [XXaddress2].[Longitude],
    [XXaddress2].[Name],
    [XXaddress2].[PostalCode],
    [XXaddress2].[PostOfficeBox],
    [XXaddress2].[PrimaryContactName],
    [XXaddress2].[ShippingMethodCode],
    [XXaddress2].[StateOrProvince],
    [XXaddress2].[Telephone1],
    [XXaddress2].[Telephone2],
    [XXaddress2].[Telephone3],
    [XXaddress2].[UPSZone],
    [XXaddress2].[UTCOffset],
    [XXaddress3].[AddressTypeCode],
    [XXaddress3].[City],
    [XXaddress3].[Composite],
    [XXaddress3].[Country],
    [XXaddress3].[County],
    [XXaddress3].[CustomerAddressId],
    [XXaddress3].[Fax],
    [XXaddress3].[FreightTermsCode],
    [XXaddress3].[Latitude],
    [XXaddress3].[Line1],
    [XXaddress3].[Line2],
    [XXaddress3].[Line3],
    [XXaddress3].[Longitude],
    [XXaddress3].[Name],
    [XXaddress3].[PostalCode],
    [XXaddress3].[PostOfficeBox],
    [XXaddress3].[PrimaryContactName],
    [XXaddress3].[ShippingMethodCode],
    [XXaddress3].[StateOrProvince],
    [XXaddress3].[Telephone1],
    [XXaddress3].[Telephone2],
    [XXaddress3].[Telephone3],
    [XXaddress3].[UPSZone],
    [XXaddress3].[UTCOffset],
    -- ownership entries
    OwnerId = [ContactBase].OwnerId,
    OwnerName = XXowner.Name,
    OwnerYomiName =  XXowner.YomiName,
    OwnerDsc = 0, -- DSC is removed, stub it to 0
    OwnerIdType = XXowner.OwnerIdType,
    OwningUser = case 
 		when XXowner.OwnerIdType= 8 then XXowner.OwnerId
		else null
		end,
    OwningTeam = case 
 		when XXowner.OwnerIdType= 9 then XXowner.OwnerId
		else null
		end,


	[AccountId] = case 
		when [ContactBase].[ParentCustomerIdType] = 1 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerId]
		else NULL
		end,
	[AccountIdName] = case 
		when [ContactBase].[ParentCustomerIdType] = 1 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdName]
		else NULL
		end,
	[AccountIdYomiName] = case 
		when [ContactBase].[ParentCustomerIdType] = 1 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdYomiName]
		else NULL
		end,
	[ParentContactId] = case 
		when [ContactBase].[ParentCustomerIdType] = 2 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerId]
		else NULL
		end,
	[ParentContactIdName] = case 
		when [ContactBase].[ParentCustomerIdType] = 2 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdName]
		else NULL
		end,
	[ParentContactIdYomiName] = case 
		when [ContactBase].[ParentCustomerIdType] = 2 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdYomiName]
		else NULL
		end,
    -- physical attribute
    [ContactBase].[ContactId],
    [ContactBase].[DefaultPriceLevelId],
    [ContactBase].[CustomerSizeCode],
    [ContactBase].[CustomerTypeCode],
    [ContactBase].[PreferredContactMethodCode],
    [ContactBase].[LeadSourceCode],
    [ContactBase].[OriginatingLeadId],
    [ContactBase].[OwningBusinessUnit],
    [ContactBase].[PaymentTermsCode],
    [ContactBase].[ShippingMethodCode],
    [ContactBase].[ParticipatesInWorkflow],
    [ContactBase].[IsBackofficeCustomer],
    [ContactBase].[Salutation],
    [ContactBase].[JobTitle],
    [ContactBase].[FirstName],
    [ContactBase].[Department],
    [ContactBase].[NickName],
    [ContactBase].[MiddleName],
    [ContactBase].[LastName],
    [ContactBase].[Suffix],
    [ContactBase].[YomiFirstName],
    [ContactBase].[FullName],
    [ContactBase].[YomiMiddleName],
    [ContactBase].[YomiLastName],
    [ContactBase].[Anniversary],
    [ContactBase].[BirthDate],
    [ContactBase].[GovernmentId],
    [ContactBase].[YomiFullName],
    [ContactBase].[Description],
    [ContactBase].[EmployeeId],
    [ContactBase].[GenderCode],
    [ContactBase].[AnnualIncome],
    [ContactBase].[HasChildrenCode],
    [ContactBase].[EducationCode],
    [ContactBase].[WebSiteUrl],
    [ContactBase].[FamilyStatusCode],
    [ContactBase].[FtpSiteUrl],
    [ContactBase].[EMailAddress1],
    [ContactBase].[SpousesName],
    [ContactBase].[AssistantName],
    [ContactBase].[EMailAddress2],
    [ContactBase].[AssistantPhone],
    [ContactBase].[EMailAddress3],
    [ContactBase].[DoNotPhone],
    [ContactBase].[ManagerName],
    [ContactBase].[ManagerPhone],
    [ContactBase].[DoNotFax],
    [ContactBase].[DoNotEMail],
    [ContactBase].[DoNotPostalMail],
    [ContactBase].[DoNotBulkEMail],
    [ContactBase].[DoNotBulkPostalMail],
    [ContactBase].[AccountRoleCode],
    [ContactBase].[TerritoryCode],
    [ContactBase].[IsPrivate],
    [ContactBase].[CreditLimit],
    [ContactBase].[CreatedOn],
    [ContactBase].[CreditOnHold],
    [ContactBase].[CreatedBy],
    [ContactBase].[ModifiedOn],
    [ContactBase].[ModifiedBy],
    [ContactBase].[NumberOfChildren],
    [ContactBase].[ChildrensNames],
    [ContactBase].[VersionNumber],
    [ContactBase].[MobilePhone],
    [ContactBase].[Pager],
    [ContactBase].[Telephone1],
    [ContactBase].[Telephone2],
    [ContactBase].[Telephone3],
    [ContactBase].[Fax],
    [ContactBase].[Aging30],
    [ContactBase].[StateCode],
    [ContactBase].[Aging60],
    [ContactBase].[StatusCode],
    [ContactBase].[Aging90],
    [ContactBase].[PreferredSystemUserId],
    [ContactBase].[PreferredServiceId],
    [ContactBase].[MasterId],
    [ContactBase].[PreferredAppointmentDayCode],
    [ContactBase].[PreferredAppointmentTimeCode],
    [ContactBase].[DoNotSendMM],
    [ContactBase].[ParentCustomerId],
    [ContactBase].[Merged],
    [ContactBase].[ExternalUserIdentifier],
    [ContactBase].[SubscriptionId],
    [ContactBase].[PreferredEquipmentId],
    [ContactBase].[LastUsedInCampaign],
    [ContactBase].[ParentCustomerIdName],
    [ContactBase].[ParentCustomerIdType],
    [ContactBase].[TransactionCurrencyId],
    [ContactBase].[OverriddenCreatedOn],
    [ContactBase].[ExchangeRate],
    [ContactBase].[ImportSequenceNumber],
    [ContactBase].[TimeZoneRuleVersionNumber],
    [ContactBase].[UTCConversionTimeZoneCode],
    [ContactBase].[AnnualIncome_Base],
    [ContactBase].[CreditLimit_Base],
    [ContactBase].[Aging60_Base],
    [ContactBase].[Aging90_Base],
    [ContactBase].[Aging30_Base],
    [ContactBase].[ParentCustomerIdYomiName],
    [ContactBase].[CreatedOnBehalfBy],
    [ContactBase].[ModifiedOnBehalfBy],
    [ContactBase].[IsAutoCreate],
    [ContactBase].[StageId],
    [ContactBase].[ProcessId],
    [ContactBase].[EntityImageId],
    [ContactBase].[TraversedPath],
    [ContactBase].[SLAId],
    [ContactBase].[SLAInvokedId],
    [ContactBase].[OnHoldTime],
    [ContactBase].[LastOnHoldTime],
    [ContactBase].[FollowEmail],
    [ContactBase].[TimeSpentByMeOnEmailAndMeetings],
    [ContactBase].[Business2],
    [ContactBase].[Callback],
    [ContactBase].[Company],
    [ContactBase].[Home2],
    [ContactBase].[CreatedByExternalParty],
    [ContactBase].[ModifiedByExternalParty],
    [ContactBase].[MarketingOnly],    
    [ContactBase].[msdyn_gdproptout]
from [ContactBase] 
    left join [CustomerAddressBase] XXaddress1 on ([ContactBase].[ContactId] = XXaddress1.ParentId and XXaddress1.AddressNumber = 1)
    left join [CustomerAddressBase] XXaddress2 on ([ContactBase].[ContactId] = XXaddress2.ParentId and XXaddress2.AddressNumber = 2)
    left join [CustomerAddressBase] XXaddress3 on ([ContactBase].[ContactId] = XXaddress3.ParentId and XXaddress3.AddressNumber = 3)
    left join [ContactBase] [contact_master_contact] on ([ContactBase].[MasterId] = [contact_master_contact].[ContactId])
    left join [LeadBase] [contact_originating_lead] on ([ContactBase].[OriginatingLeadId] = [contact_originating_lead].[LeadId])
    left join [EquipmentBase] [equipment_contacts] on ([ContactBase].[PreferredEquipmentId] = [equipment_contacts].[EquipmentId])    
    left join [ImageDescriptor] [lk_contact_entityimage] on ([ContactBase].[EntityImageId] = [lk_contact_entityimage].[ImageDescriptorId])
    left join [SystemUserBase] [lk_contactbase_createdby] with(nolock) on ([ContactBase].[CreatedBy] = [lk_contactbase_createdby].[SystemUserId])
    left join [SystemUserBase] [lk_contactbase_createdonbehalfby] with(nolock) on ([ContactBase].[CreatedOnBehalfBy] = [lk_contactbase_createdonbehalfby].[SystemUserId])
    left join [SystemUserBase] [lk_contactbase_modifiedby] with(nolock) on ([ContactBase].[ModifiedBy] = [lk_contactbase_modifiedby].[SystemUserId])
    left join [SystemUserBase] [lk_contactbase_modifiedonbehalfby] with(nolock) on ([ContactBase].[ModifiedOnBehalfBy] = [lk_contactbase_modifiedonbehalfby].[SystemUserId])
    left join [ExternalPartyBase] [lk_externalparty_contact_createdby] on ([ContactBase].[CreatedByExternalParty] = [lk_externalparty_contact_createdby].[ExternalPartyId])
    left join [ExternalPartyBase] [lk_externalparty_contact_modifiedby] on ([ContactBase].[ModifiedByExternalParty] = [lk_externalparty_contact_modifiedby].[ExternalPartyId])
    left join [SLABase] [manualsla_contact] on ([ContactBase].[SLAId] = [manualsla_contact].[SLAId] and [manualsla_contact].OverwriteTime = 0 and [manualsla_contact].ComponentState = 0)
    left join [SystemUserBase] [new_systemuser_contact_RetainedBy] with(nolock) on ([ContactBase].[new_RetainedBy] = [new_systemuser_contact_RetainedBy].[SystemUserId])    
    left join [SLABase] [sla_contact] on ([ContactBase].[SLAInvokedId] = [sla_contact].[SLAId] and [sla_contact].OverwriteTime = 0 and [sla_contact].ComponentState = 0)
    left join [SystemUserBase] [system_user_contacts] with(nolock) on ([ContactBase].[PreferredSystemUserId] = [system_user_contacts].[SystemUserId])
    left join [TransactionCurrencyBase] [transactioncurrency_contact] on ([ContactBase].[TransactionCurrencyId] = [transactioncurrency_contact].[TransactionCurrencyId])
    left join OwnerBase XXowner with(nolock) on ([ContactBase].OwnerId = XXowner.OwnerId)