Monday, January 05, 2015

Calling FIM debug.MakeCurrentUserAdministrator with the SQL PowerShell Module

Follow-up to this post:

debug.MakeCurrentUserAdministrator

I’ve been working on deploying FIM configurations with PowerShell Desired State Configuration (DSC) and to ring in the new year I started by wiping out the members of the administrators Set (oops).

The VMs I use for FIM don’t have SQL Management Studio installed (it just encourages them) so I needed a quick way to add myself back to the FIM administrators Set.

The quick solution is to call the stored procedure using the SQL PowerShell Module.  The only catch is that the stored procedure broke a while back (FIM R2 SP1 I think) so I just include the working T-SQL. 

Invoke-Sqlcmd -Database FimService -QueryTimeout 0 -Query @'

DECLARE

    @administratorsSetKey         BIGINT,

    @displayNameKey               SMALLINT,

    @groupAdministratorsSetKey    BIGINT,

    @nonAdministratorsSetKey      BIGINT,

    @setObjectTypeKey             SMALLINT,

    @userKey                      BIGINT,

    @explicitMemberAttributeKey   SMALLINT,

    @computedMemberAttributeKey   SMALLINT;      

 

SET @explicitMemberAttributeKey = [fim].[AttributeKeyFromName]  (N'ExplicitMember');

SET @computedMemberAttributeKey = [fim].[AttributeKeyFromName]  (N'ComputedMember');

SET @displayNameKey             = [fim].[AttributeKeyFromName]  (N'DisplayName');

SET @setObjectTypeKey           = [fim].[ObjectTypeKeyFromName] (N'Set');

 

SELECT @userKey = [UserObjectKey] FROM [fim].[UserSecurityIdentifiers]

WHERE [SecurityIdentifier] = SUSER_SID();

 

SELECT @administratorsSetKey = [ObjectKey] FROM [fim].[ObjectValueString] AS [ovs]

WHERE   [ovs].[ObjectTypeKey] = @setObjectTypeKey

    AND [ovs].[AttributeKey]  = @displayNameKey

    AND [ovs].[ValueString]   = N'Administrators';

 

SELECT @nonAdministratorsSetKey = [ObjectKey] FROM [fim].[ObjectValueString] AS [ovs]

WHERE   [ovs].[ObjectTypeKey] = @setObjectTypeKey

    AND [ovs].[AttributeKey]  = @displayNameKey

    AND [ovs].[ValueString]   = N'All Non-Administrators';

 

SELECT @groupAdministratorsSetKey = [ObjectKey] FROM [fim].[ObjectValueString] AS [ovs]

WHERE   [ovs].[ObjectTypeKey] = @setObjectTypeKey

    AND [ovs].[AttributeKey]  = @displayNameKey

    AND [ovs].[ValueString]   = N'Group Administrators';

 

SELECT @userKey                    as 'User Key'

SELECT @administratorsSetKey       as 'Administrator Set Key'

SELECT @nonAdministratorsSetKey    as 'Non-Administrator Set Key'

SELECT @groupAdministratorsSetKey  as 'Group Administrator Set Key'

SELECT @explicitMemberAttributeKey as 'ExplicitMember Attribute Key'

SELECT @computedMemberAttributeKey as 'ComputedMember Attribute Key'

SELECT @setObjectTypeKey           as 'Set Object Type Key'

 

EXECUTE [debug].[RemoveSetMember]

    @setKey    = @administratorsSetKey,

    @memberKey = @userKey;

 

INSERT INTO [fim].[ObjectValueReference]

(

    [ObjectKey],

    [ObjectTypeKey],

    [AttributeKey],

    [ValueReference],

      [Multivalued]

)

VALUES

(

    @administratorsSetKey,

    @setObjectTypeKey,

    @explicitMemberAttributeKey,

    @userKey,

      0

);

   

INSERT INTO [fim].[ObjectValueReference]

(

    [ObjectKey],

    [ObjectTypeKey],

    [AttributeKey],

    [ValueReference],

      [Multivalued]

)

VALUES

(

    @administratorsSetKey,

    @setObjectTypeKey,

    @computedMemberAttributeKey,

    @userKey,

      0

);

 

EXECUTE [debug].[DisplaySetMembers] @setKey = @administratorsSetKey

'@ -Verbose

 

No comments: