KB 2525307 for 2012

All Replies

• Monday, August 06, 2012 12:35 PM

   

   
  

  0
  Have you tried installing that one?  Im just curios for my own interest.

  ---

  - Get on the floor, do that dinosaur

  • Reply
  • Quote

   
• Monday, August 06, 2012 1:12 PM

   

   
  

  1

  Yes I tried and got this error:
  Unknown error (0x66a)

  • Reply
  • Quote

   
• Tuesday, August 07, 2012 10:33 AM

   

   
  

  0

  Any Update for this...

  • Reply
  • Quote

   
• Tuesday, August 07, 2012 11:06 AM

  Moderator

   

   
  

  0
  Did you check if the hotfix or solution is maybe included in the "Update Rollup 2 for SCSM 2012"? -> Update Rollup 2 for System Center 2012 Service Manager (KB2719827)

  ---

  Andreas Baumgarten | H&D International Group

  • Reply
  • Quote

   
• Tuesday, August 07, 2012 12:33 PM

   

   
  

  0

  Unfortunately Andreas it is not included.

  • Reply
  • Quote

   
• Wednesday, December 12, 2012 5:02 PM

   

   
  

  0

  Hi ! Did you ever get a solution for this ?

  R.

  ---

  Problem talk creates problems, solution talk creates solutions (Steve deShazer)

  • Reply
  • Quote

   
• Wednesday, December 12, 2012 7:50 PM

  Moderator

   

   
  

  0

  All this hotfix does is run a .sql script to extend the ProfileOperationImplicitScope table to add an IsCustomized column (if it doesn't already exist). In 2012, this column exists by default.

  You can extract the .sql file (it will have a strange name) from the installer and run on your 2012 system. It will add the stored procedures p_AddRestrictrictionToOperationInProfile and dbo.p_RemoveRestrictrictionFromOperationInProfile.

  You can use these as per the 2010 hotfix instructions to make your changes. All changes are made by inserting one or more rows into the ProfileOperationImplicitScope table with IsCustomized=1.

  ---

  Rob Ford scsmnz.net
  Cireson www.cireson.com
  For a free SCSM 2012 Auto-Close app click here
  

  • Reply
  • Quote

   
• Friday, December 21, 2012 6:39 AM

   

   
  

  1

  Hi !

  I extracted the SQL File out of the KB. Use at your own risk !

  --
  -- Adding IsCustomized column to ProfileOperationImplicitScope table.
  -- This column is used to track customizations, so that we do allow 
  -- only customized rows to be deleted.
  --
  IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'ProfileOperationImplicitScope'
                 AND COLUMN_NAME = N'IsCustomized')
  BEGIN
      ALTER TABLE dbo.ProfileOperationImplicitScope ADD [IsCustomized] bit NULL 
          CONSTRAINT [DF_ProfileOperationImplicitScope_IsCustomized] DEFAULT(NULL)
  END
  GO
  
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'p_GetRestrictrictionsOnOperationsInProfile' AND ROUTINE_TYPE = 'PROCEDURE')
      DROP PROCEDURE dbo.p_GetRestrictrictionsOnOperationsInProfile
  GO
  
  CREATE PROCEDURE dbo.p_GetRestrictrictionsOnOperationsInProfile
  (
      @ProfileName nvarchar(max) = NULL
  )
  AS
  BEGIN    
  
  	-- Run the following sproc to see the rights configured to an 
  	-- existing profile. 
  	-- Specifying NULL for profile name will give you all profiles.
  	-- How to read the results:
  	-- For each profile, you will see the operation and the type that
  	-- the operation is restricted to and further, you will see the property 
  	-- or relationship it is restricted to.
  	-- If you see a type and a property it means that the operation is 
  	-- restricted to the property on the type.
  	-- If you see a type and a relationship it means that the operation is
  	-- restricted to the relationship endpoint on the type.
  	-- Relationships are authorized as properties on both endpoints.
  	--
  	-- How to interpret null values:
  	-- For an operation, if the type is null, and the property is null and the
  	-- relationship endpoint is null, it means that the profile allows 
  	-- the operation on all types, all properties and relationship.
  	--
  	-- For E.g. #1
  	-- ProfileName      Operation   Type    Property  Relationship RelationshipEndPoint
  	-- IncidentResolver	Object__Get	NULL	NULL	  NULL	       NULL
  	--
  	-- The row above indicates that the Object__Get (Read) operation for
  	-- the IncidentResolver profile is unrestricted.
  	--
  	-- For E.g. #2
  	-- ProfileName      Operation   Type                                   Property  Relationship RelationshipEndPoint
  	-- IncidentResolver	Object__Set	System.WorkItem.Incident                NULL	 NULL	      N/A
  	-- IncidentResolver	Object__Set	System.FileAttachment                   NULL	 NULL	      N/A
  	-- IncidentResolver	Object__Set	System.WorkItem.Log                     NULL	 NULL	      N/A
  	-- IncidentResolver	Object__Set	System.WorkItem.Activity.ManualActivity	NULL	 NULL	      N/A
  	--
  	-- The rows above indicate that the Object__Set operation for 
  	-- the IncidentResolver profile is restricted to the types 
  	-- System.WorkItem.Incident and all its properties including relationship endpoints.
  	-- System.FileAttachment and all its properties including relationship endpoints.
  	-- System.WorkItem.Log and all its properties including relationship endpoints
  	-- System.WorkItem.Activity.ManualActivity and its properties including relationship endpoints.
  	-- 
  	-- For E.g. #3
  	-- ProfileName      Operation   Type                Property      Relationship                  RelationshipEndPoint
      -- ImpliedReviewer	Object__Set	System.Reviewer	    Comments	  NULL	                        N/A
      -- ImpliedReviewer	Object__Set	System.Reviewer	    DecisionDate  NULL	                        N/A
      -- ImpliedReviewer	Object__Set	System.Reviewer	    Decision	  NULL	                        N/A
      -- ImpliedReviewer	Object__Set	System.Reviewer	    NULL	      System.ReviewerVotedByUser	N/A
      -- ImpliedReviewer	Object__Set	System.Domain.User	NULL	      System.ReviewerVotedByUser	N/A
  	--
  	-- The rows above indicate that the Object__Set operation for 
  	-- the ImpliedReviewer profile is restricted to the types 
  	-- System.Reviewer and only properties Comments, DecisionDate and Decision.
  	-- System.Reviewer and only relationship endpoint System.ReviewerVotedByUser
  	-- System.Domain.User and only relationship endpoint System.ReviewerVotedByUser
  	-- 
  	-- Note how System.ReviewerVotedByUser Object__Set (Update) rights were granted to 
  	-- both endpoints System.Reviewer & System.Domain.User.
  	-- Without granting relationship rights to both endpoints you will not be able to
  	-- update reviewer objects with this relationship.
  	-- 
  	-- If customers extend the type with relationships they will need to add
  	-- the type to the operation in the profile.
  	-- For E.g. If they add a relationship System.CallingUser between 
  	-- System.WorkItem.Incident and System.Domain.User, and they want the 
  	-- IncidentResolver profile to be able to update this, they will 
  	-- need to add this relationship to the System.Domain.User endpoint's 
  	-- Object__Set (Update) right. 
  	-- There is no need to add this to the System.WorkItem.Incident endpoint's
  	-- Object__Set (Update) right, since the System.WorkItem.Incident already 
  	-- has Object__Set (Update) rights on all it's properties and relationship 
  	-- endpoints, as indicated by the entry below:
  	-- ProfileName      Operation   Type                                   Property  Relationship RelationshipEndPoint
  	-- IncidentResolver	Object__Set	System.WorkItem.Incident                NULL	 NULL	      N/A
  	--
  	-- Calling the follwoing sproc
  	-- exec p_RemoveRestrictrictionFromOperationInProfile 'IncidentResolver', 'Object__Set', 'System.Domain.User', NULL, 'System.CallingUser', NULL
  	-- will add Object__Set (Update) right to System.Domain.User for relationship System.CallingUser, in the IncidentResolver profile.
  
  	--
      -- Validate the input
  
      IF @ProfileName IS NULL
      BEGIN
          SET @ProfileName = '%'
      END
      ELSE
      BEGIN
          DECLARE @ProfileId AS UNIQUEIDENTIFIER
  		SELECT @ProfileId = P.ProfileId
  		FROM dbo.[Profile] AS P
  		WHERE P.ProfileName = @ProfileName
  
  		IF @@error <> 0 
  		BEGIN
  			PRINT N'Error while fetching the Profile. Error code: ' + CAST(@@error AS NVARCHAR)
  			GOTO Err;
  		END
  
  		IF @ProfileId IS NULL
  		BEGIN
  			PRINT N'Could not find a Profile with the specified Profile Name: ' + @ProfileName
  			GOTO Err;
  		END
      END    
  
  	SELECT 
  	P.ProfileName, 
  	O.OperationName,
  	MT.TypeName,
  	MTP.ManagedTypePropertyName,
  	RT.RelationshipTypeName,
  	CASE 
           WHEN POIS.RelationshipEndpoint = 2 THEN 'N/A'
           WHEN POIS.RelationshipEndpoint = 4 THEN 'Source'
           WHEN POIS.RelationshipEndpoint = 8 THEN 'Target'
           WHEN POIS.RelationshipEndpoint IS NULL  THEN 'N/A'
           ELSE 'Invalid'
      END AS RelationshipEndpoint,
      POIS.IsCustomized
  	FROM dbo.[Profile] AS P
  	INNER JOIN dbo.[ProfileOperation] AS PO
  	ON P.ProfileId = PO.ProfileId
  	INNER JOIN dbo.[Operation] AS O
  	ON PO.OperationId = O.OperationId
  	-- Are the operations further restricted to a type
  	-- This restriction currently applies to only Create, Read, Update, Delete operations on instances:
  	-- Object__Add (Create)
  	-- Object__Get (Read)
  	-- Object__Set (Update)
  	-- Object__Delete (Delete)
  	LEFT OUTER JOIN dbo.[ProfileOperationImplicitScope] AS POIS
  	ON PO.ProfileOperationId = POIS.ProfileOperationId
  	LEFT OUTER JOIN dbo.[ManagedType] AS MT
  	ON POIS.TypeId = MT.ManagedTypeId
  	LEFT OUTER JOIN dbo.[ManagedTypeProperty] AS MTP
  	ON POIS.PropertyId = MTP.ManagedTypePropertyId
  	LEFT OUTER JOIN dbo.[RelationshipType] AS RT
  	ON POIS.RelationshipTypeId = RT.RelationshipTypeId
  	-- Restrict to Create, Read, Update, Delete operations
  	WHERE 128 = (O.ScopeType & 128)
  	AND P.ProfileName LIKE @ProfileName
  	ORDER BY P.ProfileName, O.OperationName
  
  Err:
   
     RETURN
     
  END
  GO
  
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'p_AddRestrictrictionToOperationInProfile' AND ROUTINE_TYPE = 'PROCEDURE')
      DROP PROCEDURE dbo.p_AddRestrictrictionToOperationInProfile
  GO
  
  CREATE PROCEDURE dbo.p_AddRestrictrictionToOperationInProfile
  (
      @ProfileName AS NVARCHAR(MAX) = null, 
      @OperationName AS NVARCHAR(MAX) = null, 
      @TypeName AS NVARCHAR(MAX) = null,
      @PropertyName AS NVARCHAR(MAX) = null,
      @RelationshipTypeName AS NVARCHAR(MAX) = null,
      @RelationshipEndpoint AS NVARCHAR(MAX) = null
  )
  AS
  BEGIN    
  
  	DECLARE @ProfileId AS UNIQUEIDENTIFIER
  	DECLARE @OperationId AS UNIQUEIDENTIFIER
  	DECLARE @ProfileOperationId AS UNIQUEIDENTIFIER
  	DECLARE @TypeId AS UNIQUEIDENTIFIER
  	DECLARE @PropertyId AS UNIQUEIDENTIFIER
  	DECLARE @RelationshipTypeId AS UNIQUEIDENTIFIER
  	DECLARE @RelationshipEndpointAsInt AS INT
  
  	-- Select the Profile
  
  	SELECT @ProfileId = P.ProfileId
  	FROM dbo.[Profile] AS P
  	WHERE P.ProfileName = @ProfileName
  
  	IF @@error <> 0 
  	BEGIN
  		PRINT N'Error while fetching the specified Profile. Error code: ' + CAST(@@error AS NVARCHAR)
  		GOTO Err;
  	END
  
  	IF @ProfileId IS NULL
  	BEGIN
     	    PRINT N'Could not find a Profile with the specified Profile Name: ' + @ProfileName
  		GOTO Err;
  	END
  
  	-- Select the Operation
  
  	SELECT @OperationId = O.OperationId
  	FROM dbo.[Operation] AS O
  	WHERE O.OperationName = @OperationName
  
  	IF @@error <> 0 
  	BEGIN
  		PRINT N'Error while fetching the specified Operation. Error code: ' + CAST(@@error AS VARCHAR)
  		GOTO Err;
  	END
  
  	IF @ProfileId IS NULL
  	BEGIN
     	    PRINT N'Could not find an Operation with the specified Operation Name: ' + @OperationName
  		GOTO Err;
  	END
  
  	-- Check if this is a correct kind of operation that you have specified.
  	-- Only the following operations in a profile can be further restricted:
  	-- Object__Add (Create) 
  	-- Object__Get (Read) 
  	-- Object__Set (Update) 
  	-- Object__Delete (Delete) 
  	
  	DECLARE @ScopeType AS INT
  
  	SELECT @ScopeType = O.ScopeType
  	FROM dbo.Operation AS O
  	WHERE O.OperationId = @OperationId
  
  	IF @@error <> 0 
  	BEGIN
  		PRINT N'Error while fetching the Operation Scope. Error code: ' + CAST(@@error AS VARCHAR)
  		GOTO Err;
  	END
  
  	IF 128 <> (@ScopeType & 128)
  	BEGIN
  		PRINT N'The specified operation cannot be further restricted by type. Only operations that have the bit 128 set on their ScopeType are allowed to be restricted by type. Only the following operations can be restricted by type: Object__Add, Object__Set, Object__Delete, Object_Get'
  		GOTO Err;
  	END
  
  	-- Select the Operation in the Profile.
  
  	SELECT @ProfileOperationId = PO.ProfileOperationId
  	FROM dbo.[ProfileOperation] AS PO
  	WHERE PO.ProfileId = @ProfileId
  	AND PO.OperationId = @OperationId
  
  	IF @@error <> 0 
  	BEGIN
  		PRINT N'Error while fetching the Operation in the specified Profile. Error code: ' + CAST(@@error AS VARCHAR)
  		GOTO Err;
  	END
  
  	IF @ProfileOperationId IS NULL
  	BEGIN
  		PRINT N'Could not find the specified Operation in the specified Profile. Profile Name: ' + @ProfileName + ' Operation Name: ' + @OperationName
  		GOTO Err;
  	END
  
  	-- Select the TypeId which you want the operation to be restricted to
  
  	SELECT @TypeId = MT.ManagedTypeId
  	FROM dbo.ManagedType AS MT
  	WHERE MT.TypeName = @TypeName
  
  	IF @@error <> 0 
  	BEGIN
  		PRINT N'Error while fetching the specified Type. Error code: ' + CAST(@@error AS NVARCHAR)
  		GOTO Err;
  	END
  
  	IF @TypeId IS NULL
  	BEGIN
  		PRINT N'Could not find a Type with the specified TypeName: ' + @TypeName
  		GOTO Err;
  	END
  
  	-- Select the property id if specified
  
      SET @PropertyId = NULL
  	IF @PropertyName IS NOT NULL
  	BEGIN
  
  		SELECT @PropertyId = MTP.ManagedTypePropertyId
  		FROM dbo.ManagedTypeProperty AS MTP
  		WHERE MTP.ManagedTypePropertyName = @PropertyName
  
  		IF @@error <> 0 
  		BEGIN
  			PRINT N'Error while fetching the specified Property. Error code: ' + CAST(@@error AS NVARCHAR)
  			GOTO Err;
  		END
  
  		IF @PropertyId IS NULL
  		BEGIN
  			PRINT N'Could not find a Property with the specified PropertyName: ' + @PropertyName
  			GOTO Err;
  		END
  	END
  
  	-- Select the relationship type id if specified
  	
  	SET @RelationshipEndpointAsInt = 2
      SET @RelationshipTypeId = NULL
  	IF @RelationshipTypeName IS NOT NULL
  	BEGIN
  		SELECT @RelationshipTypeId = RT.RelationshipTypeId
  		FROM dbo.RelationshipType AS RT
  		WHERE RT.RelationshipTypeName = @RelationshipTypeName
  
  		IF @@error <> 0 
  		BEGIN
  			PRINT N'Error while fetching the specified Relationship. Error code: ' + CAST(@@error AS NVARCHAR)
  			GOTO Err;
  		END
  
  		IF @RelationshipTypeId IS NULL
  		BEGIN
  			PRINT N'Could not find a RelationshipType with the specified RelationshipTypeName: ' + @RelationshipTypeName
  			GOTO Err;
  		END
  	    
  		-- Set the relationship endpoint
  		IF @RelationshipEndpoint IS NOT NULL
  		AND @RelationshipEndpoint = 'Source'
  		BEGIN
  			SET @RelationshipEndpointAsInt = 4
  		END
  		ELSE IF @RelationshipEndpoint IS NOT NULL
  		AND @RelationshipEndpoint = 'Target'
  		BEGIN
  			SET @RelationshipEndpointAsInt = 8
  		END
  		ELSE IF @RelationshipEndpoint IS NOT NULL
  		AND (@RelationshipEndpoint = 'N/A' OR @RelationshipEndpoint = 'Any')
  		BEGIN
  			SET @RelationshipEndpointAsInt = 2
  		END
  		ELSE IF @RelationshipEndpoint IS NOT NULL
  		BEGIN
  			PRINT N'The specified RelationshipEndpoint is invalid: ' + @RelationshipEndpoint + '. Must be N/A Source Target'
  			GOTO Err;
  		END		
  	END
  	
  	-- Ensure that either property id or relationship id are specified
  	-- and not both.
  	
  	IF @PropertyId IS NOT NULL AND @RelationshipTypeId IS NOT NULL
  	BEGIN
  	    PRINT N'Cannot specify both property and relationship at the same time. You will need to call the stored procedure seperately to add a property restriction, and to add a relationship restriction to the type. You can specify NULL for both PropertyName and RelationshipTypeName to grant the operation on all properties and all relationships in the type.'
  		GOTO Err;
  	END
  
  	-- Check if the operation is already restricted to the type in the specified 
  	-- profile
  
  	DECLARE @RestrictedCount AS INT
  
  	-- Check if there is no type restriction in the operation, in the profile.
  
  	SELECT @RestrictedCount = COUNT(*)
  	FROM dbo.ProfileOperationImplicitScope AS POIS
  	WHERE POIS.ProfileOperationId = @ProfileOperationId
  
  	IF @@error <> 0 
  	BEGIN
  		PRINT N'Error while checking for type restrictions on the Operation in the specified Profile. Error code: ' + CAST(@@error AS NVARCHAR)
  		GOTO Err;
  	END
  
  	IF 0 = @RestrictedCount 
  	BEGIN
  		PRINT N'The specified Operation has no type restrictions in the Profile. There is no need to restrict the Operation to the specified Type again.'
  		GOTO Err;
  	END
  
  	-- Check if there is already a type restriction on the operation, in the 
  	-- profile, for the same type (or a base type) and all  properties.
  
  	SET @RestrictedCount = 0
  
  	SELECT @RestrictedCount = COUNT(*)
  	FROM dbo.ProfileOperationImplicitScope AS POIS
  	INNER JOIN dbo.DerivedManagedTypes AS DMT
  	ON POIS.TypeId = DMT.BaseTypeId
  	WHERE POIS.ProfileOperationId = @ProfileOperationId
  	AND POIS.PropertyId IS NULL 
  	AND  POIS.RelationshipTypeId IS NULL
  	AND DMT.DerivedTypeId = @TypeId
  
  	IF @@error <> 0 
  	BEGIN
  		PRINT N'Error while checking for Type restrictions on the Operation in the specified Profile. Error code: ' + CAST(@@error AS NVARCHAR)
  		GOTO Err;
  	END
  
  	IF 0 <> @RestrictedCount 
  	BEGIN
  		PRINT N'The specified Operation is already restricted to the specified Type (or its Base Type) and all its properties and relationships. There is no need to restrict the Operation to the specified Type again.'
  		GOTO Err;
  	END
  
  	-- Check if there is already a type restriction on the operation, in the 
  	-- profile, for the same type and property.
  
  	SET @RestrictedCount = 0
  
  	IF @PropertyId IS NOT NULL
  	BEGIN 
  		SELECT @RestrictedCount = COUNT(*)
  		FROM dbo.ProfileOperationImplicitScope AS POIS
  		WHERE POIS.ProfileOperationId = @ProfileOperationId
  		AND POIS.TypeId = @TypeId
  		AND POIS.PropertyId = @PropertyId
  
  		IF @@error <> 0 
  		BEGIN
  			PRINT N'Error while checking for Type restrictions on the Operation in the specified Profile. Error code: ' + CAST(@@error AS NVARCHAR)
  			GOTO Err;
  		END
  
  		IF 0 <> @RestrictedCount 
  		BEGIN
  			PRINT N'The specified Operation has already been restricted to the specified Type and Property. There is no need to restrict the operation to the specified Type and Property again.'
  			GOTO Err;
  		END
  	END 
  
  	IF @RelationshipTypeId IS NOT NULL
  	BEGIN
  		SELECT @RestrictedCount = COUNT(*)
  		FROM dbo.ProfileOperationImplicitScope AS POIS
  		WHERE POIS.ProfileOperationId = @ProfileOperationId
  		AND POIS.TypeId = @TypeId
  		AND POIS.RelationshipTypeId = @RelationshipTypeId
  		AND (POIS.RelationshipEndpoint = @RelationshipEndpointAsInt OR POIS.RelationshipEndpoint = 2)
  
  		IF @@error <> 0 
  		BEGIN
  			PRINT N'Error while checking for Type restrictions on the Operation in the specified Profile. Error code: ' + CAST(@@error AS NVARCHAR)
  			GOTO Err;
  		END
  
  		IF 0 <> @RestrictedCount 
  		BEGIN
  			PRINT N'The specified Operation has already been restricted to the specified Type and Relationship type. There is no need to restrict the Operation to the specified Type and Relationship type again.'
  			GOTO Err;
  		END
  	END 
  
  	-- Add the restriction
  	INSERT INTO dbo.ProfileOperationImplicitScope
  	SELECT @ProfileOperationId AS ProfileOperationId,
  	@TypeId AS TypeId,
  	@PropertyId AS PropertyId,
      @RelationshipTypeId AS RelationshipTypeId,
  	@RelationshipEndpointAsInt AS RelationshipEndpoint,
  	1 AS IsCustomized
  
  	IF @@error <> 0 
  	BEGIN
  		PRINT N'Error while adding a customized restriction for the specified Operation with the specified TypeName: ' + @TypeName + ', specified PropertyName: ' + @PropertyName +  ', specified RelationshipTypeName: ' + @RelationshipTypeName + ' Error code: ' + CAST(@@error AS NVARCHAR)
  		GOTO Err;
  	END
  		
  Err:
  
  	RETURN 	
  END
  GO
  
  
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'p_RemoveRestrictrictionFromOperationInProfile' AND ROUTINE_TYPE = 'PROCEDURE')
      DROP PROCEDURE dbo.p_RemoveRestrictrictionFromOperationInProfile
  GO
  
  CREATE PROCEDURE dbo.p_RemoveRestrictrictionFromOperationInProfile
  (
      @ProfileName AS NVARCHAR(MAX) = null, 
      @OperationName AS NVARCHAR(MAX) = null, 
      @TypeName AS NVARCHAR(MAX) = null,
      @PropertyName AS NVARCHAR(MAX) = null,
      @RelationshipTypeName AS NVARCHAR(MAX) = null,
      @RelationshipEndpoint AS NVARCHAR(MAX) = null
  )
  AS
  BEGIN    
  	DECLARE @ProfileId AS UNIQUEIDENTIFIER
  	DECLARE @OperationId AS UNIQUEIDENTIFIER
  	DECLARE @ProfileOperationId AS UNIQUEIDENTIFIER
  	DECLARE @TypeId AS UNIQUEIDENTIFIER
  	DECLARE @PropertyId AS UNIQUEIDENTIFIER
  	DECLARE @RelationshipTypeId AS UNIQUEIDENTIFIER
  	DECLARE @RelationshipEndpointAsInt AS INT
  
  	-- Select the Profile
  
  	SELECT @ProfileId = P.ProfileId
  	FROM dbo.[Profile] AS P
  	WHERE P.ProfileName = @ProfileName
  
  	IF @@error <> 0 
  	BEGIN
  		PRINT N'Error while fetching the specified Profile. Error code: ' + CAST(@@error AS NVARCHAR)
  		GOTO Err;
  	END
  
  	IF @ProfileId IS NULL
  	BEGIN
     	    PRINT N'Could not find a Profile with the specified Profile Name: ' + @ProfileName
  		GOTO Err;
  	END
  
  	-- Select the Operation
  
  	SELECT @OperationId = O.OperationId
  	FROM dbo.[Operation] AS O
  	WHERE O.OperationName = @OperationName
  
  	IF @@error <> 0 
  	BEGIN
  		PRINT N'Error while fetching the specified Operation. Error code: ' + CAST(@@error AS VARCHAR)
  		GOTO Err;
  	END
  
  	IF @ProfileId IS NULL
  	BEGIN
     	    PRINT N'Could not find an Operation with the specified Operation Name: ' + @OperationName
  		GOTO Err;
  	END
  
  	-- Check if this is a correct kind of operation that you have specified.
  	-- Only the following operations in a profile can be further restricted:
  	-- Object__Add (Create) 
  	-- Object__Get (Read) 
  	-- Object__Set (Update) 
  	-- Object__Delete (Delete) 
  	
  	DECLARE @ScopeType AS INT
  
  	SELECT @ScopeType = O.ScopeType
  	FROM dbo.Operation AS O
  	WHERE O.OperationId = @OperationId
  
  	IF @@error <> 0 
  	BEGIN
  		PRINT N'Error while fetching the Operation Scope. Error code: ' + CAST(@@error AS VARCHAR)
  		GOTO Err;
  	END
  
  	IF 128 <> (@ScopeType & 128)
  	BEGIN
  		PRINT N'The specified Operation cannot be further restricted to a type. Only Operations that have the bit 128 set on their ScopeType are allowed type restrictions. Only following Operations are allowed to be restricted by Type: Object__Add, Object__Set, Object__Delete, Object_Get'
  		GOTO Err;
  	END
  
  	-- Select the Operation in the Profile.
  
  	SELECT @ProfileOperationId = PO.ProfileOperationId
  	FROM dbo.[ProfileOperation] AS PO
  	WHERE PO.ProfileId = @ProfileId
  	AND PO.OperationId = @OperationId
  
  	IF @@error <> 0 
  	BEGIN
  		PRINT N'Error while fetching the specified Operation in the Profile. Error code: ' + CAST(@@error AS VARCHAR)
  		GOTO Err;
  	END
  
  	IF @ProfileOperationId IS NULL
  	BEGIN
  		PRINT N'Could not find the specified Operation in the Profile. Profile Name: ' + @ProfileName + ' Operation Name: ' + @OperationName
  		GOTO Err;
  	END
  
  	-- Select the Operation in the Profile.
  
  	SELECT @ProfileOperationId = PO.ProfileOperationId
  	FROM dbo.[ProfileOperation] AS PO
  	WHERE PO.ProfileId = @ProfileId
  	AND PO.OperationId = @OperationId
  
  	IF @@error <> 0 
  	BEGIN
  		PRINT N'Error while fetching the specified Operation in the Profile. Error code: ' + CAST(@@error AS VARCHAR)
  		GOTO Err;
  	END
  
  	IF @ProfileOperationId IS NULL
  	BEGIN
  		PRINT N'Could not find the specified Operation in the Profile. Profile Name: ' + @ProfileName + ' Operation Name: ' + @OperationName
  		GOTO Err;
  	END
  
  
  	-- Select the TypeId which you want the operation to be restricted to
  
  	SELECT @TypeId = MT.ManagedTypeId
  	FROM dbo.ManagedType AS MT
  	WHERE MT.TypeName = @TypeName
  
  	IF @@error <> 0 
  	BEGIN
  		PRINT N'Error while fetching the specified Type. Error code: ' + CAST(@@error AS NVARCHAR)
  		GOTO Err;
  	END
  
  	IF @TypeId IS NULL
  	BEGIN
  		PRINT N'Could not find a Type with the specified TypeName: ' + @TypeName
  		GOTO Err;
  	END
  
  	-- Select the property id if specified
  
      SET @PropertyId = NULL
  	IF @PropertyName IS NOT NULL
  	BEGIN
  
  		SELECT @PropertyId = MTP.ManagedTypePropertyId
  		FROM dbo.ManagedTypeProperty AS MTP
  		WHERE MTP.ManagedTypePropertyName = @PropertyName
  
  		IF @@error <> 0 
  		BEGIN
  			PRINT N'Error while fetching the specified Property. Error code: ' + CAST(@@error AS NVARCHAR)
  			GOTO Err;
  		END
  
  		IF @PropertyId IS NULL
  		BEGIN
  			PRINT N'Could not find a Property with the specified PropertyName: ' + @PropertyName
  			GOTO Err;
  		END
  	END
  
  	-- Select the relationship type id if specified
  	
  	SET @RelationshipEndpointAsInt = 2
      SET @RelationshipTypeId = NULL
  	IF @RelationshipTypeName IS NOT NULL
  	BEGIN
  		SELECT @RelationshipTypeId = RT.RelationshipTypeId
  		FROM dbo.RelationshipType AS RT
  		WHERE RT.RelationshipTypeName = @RelationshipTypeName
  
  		IF @@error <> 0 
  		BEGIN
  			PRINT N'Error while fetching the specified RelationshipType. Error code: ' + CAST(@@error AS NVARCHAR)
  			GOTO Err;
  		END
  
  		IF @RelationshipTypeId IS NULL
  		BEGIN
  			PRINT N'Could not find a RelationshipType with the specified RelationshipTypeName: ' + @RelationshipTypeName
  			GOTO Err;
  		END
  	    
  		-- Set the relationship endpoint
  		IF @RelationshipEndpoint IS NOT NULL
  		AND @RelationshipEndpoint = 'Source'
  		BEGIN
  			SET @RelationshipEndpointAsInt = 4
  		END
  		ELSE IF @RelationshipEndpoint IS NOT NULL
  		AND @RelationshipEndpoint = 'Target'
  		BEGIN
  			SET @RelationshipEndpointAsInt = 8
  		END
  		ELSE IF @RelationshipEndpoint IS NOT NULL
  		AND (@RelationshipEndpoint = 'N/A' OR @RelationshipEndpoint = 'Any')
  		BEGIN
  			SET @RelationshipEndpointAsInt = 2
  		END
  		ELSE IF @RelationshipEndpoint IS NOT NULL
  		BEGIN
  			PRINT N'The specified RelationshipEndpoint is invalid: ' + @RelationshipEndpoint + '. Must be N/A or Source or Target'
  			GOTO Err;
  		END		
  	END
  
      -- Validate that you can remove the customization.		
  	DECLARE @CustomizedRestrictionCount AS INT
  	IF @PropertyId IS NOT NULL AND @RelationshipTypeId IS NOT NULL
  	BEGIN
  		-- Ensure that either property id or relationship id are specified
  		-- and not both.
  	    PRINT N'Cannot specify both Property and Relationship at the same time. You will need to call the stored procedure separately to remove a Property restriction and to remove a Relationship restriction to the type. You can specify NULL for both PropertyName and RelationshipTypeName to remove the restriction for all properties and all relationships for the type.'
  		GOTO Err;
  	END
  	ELSE IF @PropertyId IS NULL AND @RelationshipTypeId IS NULL
  	BEGIN
  		SELECT @CustomizedRestrictionCount = COUNT(*)
  		FROM dbo.ProfileOperationImplicitScope AS POIS
  		WHERE POIS.ProfileOperationId = @ProfileOperationId
  		AND POIS.TypeId = @TypeId
  		AND POIS.PropertyId IS NULL
  		AND POIS.RelationshipTypeId IS NULL
  		AND POIS.IsCustomized = 1
  		
  		IF @@error <> 0 
  		BEGIN
  			PRINT N'Error while finding a customized Type restriction for the specified Operation with the specified TypeName: ' + @TypeName + ' Error code: ' + CAST(@@error AS NVARCHAR)
  			GOTO Err;
  		END
  
  		IF 0 = @CustomizedRestrictionCount 
  		BEGIN
  			PRINT N'Unable to find a customized Type restriction for the specified Operation with the specified TypeName: ' + @TypeName + '. Only customized restrictions can be removed.'
  			GOTO Err;
  		END
  		
  		DELETE FROM dbo.ProfileOperationImplicitScope 
  		WHERE ProfileOperationId = @ProfileOperationId
  		AND TypeId = @TypeId
  		AND PropertyId IS NULL
  		AND RelationshipTypeId IS NULL
  		AND IsCustomized = 1
  
  		IF @@error <> 0 
  		BEGIN
  			PRINT N'Error while removing a customized Type restriction for the specified Operation with the specified TypeName: ' + @TypeName + ' Error code: ' + CAST(@@error AS NVARCHAR)
  			GOTO Err;
  		END
  		
  	END
  	ELSE IF @PropertyId IS NOT NULL
  	BEGIN
  		SELECT @CustomizedRestrictionCount = COUNT(*)
  		FROM dbo.ProfileOperationImplicitScope AS POIS
  		WHERE POIS.ProfileOperationId = @ProfileOperationId
  		AND POIS.TypeId = @TypeId
  		AND POIS.PropertyId = @PropertyId
  		AND POIS.IsCustomized = 1
  
  		IF @@error <> 0 
  		BEGIN
  			PRINT N'Error while finding a customized Property restriction for the specified Operation with the specified TypeName: ' + @TypeName + ', specified PropertyName: ' + @PropertyName + ' Error code: ' + CAST(@@error AS NVARCHAR)
  			GOTO Err;
  		END
  
  		IF 0 = @CustomizedRestrictionCount 
  		BEGIN
  			PRINT N'Unable to find a customized Property restriction for the specified Operation with the specified TypeName: ' + @TypeName + ', specified PropertyName: ' + @PropertyName + '. Only customized restrictions can be removed.'
  			GOTO Err;
  		END
  		
  		DELETE FROM dbo.ProfileOperationImplicitScope 
  		WHERE ProfileOperationId = @ProfileOperationId
  		AND TypeId = @TypeId
  		AND PropertyId = @PropertyId
  		AND IsCustomized = 1
  
  		IF @@error <> 0 
  		BEGIN
  			PRINT N'Error while removing a customized Property restriction for the specified Operation with the specified TypeName: ' + @TypeName + ', specified PropertyName: ' + @PropertyName + ' Error code: ' + CAST(@@error AS NVARCHAR)
  			GOTO Err;
  		END
  
  	END
  	ELSE IF @RelationshipTypeId IS NOT NULL
  	BEGIN
  
  		SELECT @CustomizedRestrictionCount = COUNT(*)
  		FROM dbo.ProfileOperationImplicitScope AS POIS
  		WHERE POIS.ProfileOperationId = @ProfileOperationId
  		AND POIS.TypeId = @TypeId
  		AND POIS.RelationshipTypeId = @RelationshipTypeId
  		AND POIS.RelationshipEndpoint = @RelationshipEndpointAsInt
  		AND POIS.IsCustomized = 1
  
  		IF @@error <> 0 
  		BEGIN
  			PRINT N'Error while finding a customized Relationship restriction for the specified Operation with the specified TypeName: ' + @TypeName + ', specified RelationshipTypeName: ' + @RelationshipTypeName + ' Error code: ' + CAST(@@error AS NVARCHAR)
  			GOTO Err;
  		END
  
  		IF 0 = @CustomizedRestrictionCount 
  		BEGIN
  			PRINT N'Unable to find a customized Relationship restriction for the specified Operation with the specified TypeName: ' + @TypeName + ', specified RelationshipTypeName: ' + @RelationshipTypeName + '. Only customized restrictions can be removed.'
  			GOTO Err;
  		END
  		
  		DELETE FROM dbo.ProfileOperationImplicitScope
  		WHERE ProfileOperationId = @ProfileOperationId
  		AND TypeId = @TypeId
  		AND RelationshipTypeId = @RelationshipTypeId
  		AND RelationshipEndpoint = @RelationshipEndpointAsInt
  		AND IsCustomized = 1
  
  		IF @@error <> 0 
  		BEGIN
  			PRINT N'Error while removing a customized Relationship restriction for the specified Operation with the specified TypeName: ' + @TypeName + ', specified RelationshipTypeName: ' + @RelationshipTypeName + ' Error code: ' + CAST(@@error AS NVARCHAR)
  			GOTO Err;
  		END
  	END
  		
  Err:
  
  	RETURN 	
  END
  GO
  

  
  

  ---

  Problem talk creates problems, solution talk creates solutions (Steve deShazer)

  • Reply
  • Quote