BEGIN TRY
	BEGIN TRAN

	DECLARE @PermissionDataSource TABLE (
		permissionId uniqueidentifier, 
		name varchar(max),
		roleId uniqueidentifier)

	-- Ids von Berechtigung und neuer Rolle zwischenspeichern
	INSERT INTO @PermissionDataSource
	SELECT PERID, PER_name, NEWID()
	FROM [dbo].[TPERMISSION]

	-- Rollen erstellen
	INSERT INTO [dbo].[TROLE] ([Id], [Name] , [deleted], [createdAt], [createdBy], [changedAt], [changedBy])
	SELECT roleId, '[GENERIERT] '+name, 0, SYSDATETIME(), NULL, SYSDATETIME(), NULL
	FROM @PermissionDataSource

	-- Berechtigungen zu neu erstellten Rollen hinzufügen
	INSERT INTO [dbo].[TROLE_PERMISSION] ([Id], [RoleId], [PermissionId], [deleted], [createdAt], [createdBy], [changedAt], [changedBy])
	SELECT NEWID(), roleId, permissionId, 0, SYSDATETIME(), NULL, SYSDATETIME(), NULL
	FROM @PermissionDataSource

	-- Für vergebene Berechtigungen an Benutzer Rollen zuteilen
	INSERT INTO [dbo].[TROLE_USER_ASSIGNMENT] ([Id], [RoleId], [UserId], [UnitId], [deleted], [createdAt], [createdBy], [changedAt], [changedBy])
	SELECT NEWID(), d.roleId, piu.[UID], i.[OBJID], 0, SYSDATETIME(), NULL, SYSDATETIME(), NULL
	FROM TPERMISSION_INSTANCE_TUSER piu
	JOIN TPERMISSION_INSTANCE i ON piu.PERINSTID = i.PERINSTID
	JOIN @PermissionDataSource d ON i.PERID = d.permissionId
	WHERE (EXISTS (SELECT * FROM TUNIT u WHERE u.UnitId = i.[OBJID]) OR i.[OBJID] = '00000000-0000-0000-0000-000000000000')
	AND EXISTS (SELECT * FROM TUSER us WHERE us.userid = piu.UID)

	-- Für vergebene Berechtigungen an Benutzergrupen Rollen zuteilen
	INSERT INTO [dbo].[TROLE_USERGROUP_ASSIGNMENT] ([Id], [RoleId], [UsergroupId], [UnitId], [deleted], [createdAt], [createdBy], [changedAt], [changedBy])
	SELECT NEWID(), d.roleId, piug.UGID, i.[OBJID], 0, SYSDATETIME(), NULL, SYSDATETIME(), NULL
	FROM TPERMISSION_INSTANCE_TUSERGROUP piug
	JOIN TPERMISSION_INSTANCE i ON piug.PERINSTID = i.PERINSTID
	JOIN @PermissionDataSource d ON i.PERID = d.permissionId
	WHERE (EXISTS (SELECT * FROM TUNIT u WHERE u.UnitId = i.[OBJID]) OR i.[OBJID] = '00000000-0000-0000-0000-000000000000')
	AND EXISTS (SELECT * FROM TUSERGROUP ug WHERE ug.UGID = piug.UGID)

	COMMIT

END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0
	BEGIN
		ROLLBACK TRANSACTION
	END
END CATCH