locked
KB 2525307 for 2012 RRS feed

  • Question

  • Hi,

    I have the same authorization issue that is described in article KB 2525307 ( http://support.microsoft.com/kb/2525307 )

    but I am using SCSM 2012, is there an equivalent hotfix for SCSM 2012

    This is really important



    • Edited by Alomari Monday, August 6, 2012 12:16 PM
    Monday, August 6, 2012 12:14 PM

Answers

  • 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)

    Friday, December 21, 2012 6:39 AM

All replies

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

    - Get on the floor, do that dinosaur

    Monday, August 6, 2012 12:35 PM
  • Yes I tried and got this error:
    Unknown error (0x66a)

    Monday, August 6, 2012 1:12 PM
  • Any Update for this...

    Tuesday, August 7, 2012 10:33 AM
  • 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

    Tuesday, August 7, 2012 11:06 AM
  • Unfortunately Andreas it is not included.

    Tuesday, August 7, 2012 12:33 PM
  • Hi ! Did you ever get a solution for this ?

    R.


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

    Wednesday, December 12, 2012 5:02 PM
  • 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

    Wednesday, December 12, 2012 7:50 PM
  • 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)

    Friday, December 21, 2012 6:39 AM