/*

Pentana Audit - Reset Audience for all SYSTEM-owned tiles.sql

Written by Ideagen (email: support.pentana-audit@ideagen.com)

Purpose of script:
	It is possible to inadvertently set a tile that is owned by SYSTEM to private, and this means that the tile disappears from the gallery completely.
	
	This script will reset ANY tile that has been hidden in this way, i.e. belongs to SYSTEM, is not currently globally visible.

Changes:
	Renamed file - was previously called 'Pentana Audit - Reset Audience for all tiles.sql', renamed to make clear that this applies specifically to files not owned by real people, 
	only by SYSTEM (and NULL)

To run:

- stop the Pentana Audit robot service
- stop the Pentana application pools
- if Retain is used, stop the Retain Server service
- back up the Pentana database and verify
- if Retain is used, back up the Retain database and verify
- run this script - it should run without error
- restart the Pentana app pools
- restart the Pentana Audit robot service
- if Retain is used, restart the Retain service
- verify effect of change
 
*/

-- Reset the visibility settings for all tiles not owned by a real person
	UPDATE UITile SET [Public] = 1, [Global] = 1 WHERE OwnerID = 1 OR OwnerID IS NULL;

-- Register this in the Installlog
	-- Find whether the last entry was an install or upgrade and manage the FromVersion and ToVersion appropiately
	DECLARE @FromVersion NVARCHAR(25) = (SELECT TOP 1 FromVersion FROM InstallLog ORDER BY ID DESC);
	DECLARE @ToVersion NVARCHAR(25) = (SELECT TOP 1 ToVersion FROM InstallLog ORDER BY ID DESC);

	IF @ToVersion IS NULL
		INSERT INTO InstallLog (GUID, Name, Type, Installed, FromVersion, ToVersion, [User]) VALUES (NewID(), 'Pentana Audit - Reset Audience for all SYSTEM-owned tiles.sql', 'Script', GETDATE(), @FromVersion, NULL, SYSTEM_USER);
	ELSE
		INSERT INTO InstallLog (GUID, Name, Type, Installed, FromVersion, ToVersion, [User]) VALUES (NewID(), 'Pentana Audit - Reset Audience for all SYSTEM-owned tiles.sql', 'Script', GETDATE(), @ToVersion, NULL, SYSTEM_USER);
	GO