"trufaux" wrote:

> What follows is a trigger I wrote to mirror the MbfUser to the MbfRole table
> in my Business Portal database.  I send a report to one of these roles and
> for some reason it doesn't show until after I do an IISReset on my server.
>
> /*Because of a constraint on MbfRoleUser, MbfRoleUser must be cleared out
> before MbfRole.*/
>
> /*Delete MbfRoleUser records with corresponding MbfRole records with
> Descriptions like "Report Recipient..."*/
> DELETE FROM dbo.MbfRoleUser WHERE RoleID IN (SELECT ID FROM dbo.MbfRole
> WHERE Description LIKE "Report Recipient: %")
>
> /*Delete Report Recipient Roles not in MbfUser*/
> DELETE FROM dbo.MbfRole WHERE Description LIKE "Report Recipient: %" AND
> Name IN (SELECT Name from dbo.MbfUser)
>
> /*Delete MbfDynamicViewSecurity records no longer in MbfRole*/
> DELETE FROM dbo.MbfDynamicViewSecurity WHERE RightsID NOT IN (SELECT ID FROM
> dbo.MbfRole)
>
> /*Delete MbfWSSCSGroupPart records no longer in MbfRole*/
> DELETE FROM dbo.MbfWSSCSGroupPart WHERE BusinessPortalID NOT IN (SELECT ID
> FROM dbo.MbfRole)
>
> /*Insert into the MbfRole table records from the MbfUser table that are not
> in the MbfRole table (excepting Administrator -- that's already in there)*/
> INSERT INTO dbo.MbfRole (ID, Name, Description, ConstituentType, TypeID)
> SELECT NEWID(), Name, "Report Recipient: " + Name,
> "Microsoft.Solomon.SystemManager.SolomonUser", 1 FROM dbo.MbfUser WHERE Name
> <> 'Administrator' AND Name NOT IN (SELECT Name FROM dbo.MbfRole)
>
> /*Give all the new users (those not in the MbfDynamicViewSecurity table)
> Report Request Data Permissions*/
> INSERT INTO dbo.MbfDynamicViewSecurity (DynamicViewID, RightsID,
> Permissions) SELECT 'A44CD000-302E-447A-8D70-D505C50C3E3A', dbo.MbfRole.ID,
> 36 FROM dbo.MbfRole WHERE dbo.MbfRole.ID NOT IN (SELECT RightsID FROM
> dbo.MbfDynamicViewSecurity)
>
> /*Insert into the MbfWSSCSGroupPart table records from the MbfUser table
> that aren't in the MbfWSSCSGroupPart table*/
> INSERT INTO dbo.MbfWSSCSGroupPart (ID, BusinessPortalID, Name, TypeID)
> SELECT NEWID(), ID, Name, 0 FROM dbo.MbfRole WHERE Name <> 'Administrator'
> AND ID NOT IN (SELECT BusinessPortalID FROM dbo.MbfWSSCSGroupPart)
>
> /*Insert into the MbfRoleUser table records from the MbfUser table
> (excepting the Administrator)*/
> INSERT INTO dbo.MbfRoleUser (UserID, RoleID, ContainerID, Container, TypeID)
> SELECT dbo.MbfUser.ID, dbo.MbfRole.ID, 
> '200D3CE8-F6E0-4113-8D63-5339EE5D88AA',
> '<entityKey><Microsoft.Solomon.Common.Company.Company ID="EPRIZE"
> /></entityKey>', 1 FROM dbo.MbfRole INNER JOIN dbo.MbfUser ON
> dbo.MbfRole.Name = dbo.MbfUser.Name WHERE dbo.MbfRole.Name <> 'Administrator'
>
> Thanks for your help

"John Bell" wrote:

> Hi
>
> I assume that it is not IISReset that is making this work, but the
> re-connection to the database.
>
> John
>
> "trufaux" wrote:
>
> > What follows is a trigger I wrote to mirror the MbfUser to the MbfRole table
> > in my Business Portal database.  I send a report to one of these roles and
> > for some reason it doesn't show until after I do an IISReset on my server.
> >
> > /*Because of a constraint on MbfRoleUser, MbfRoleUser must be cleared out
> > before MbfRole.*/
> >
> > /*Delete MbfRoleUser records with corresponding MbfRole records with
> > Descriptions like "Report Recipient..."*/
> > DELETE FROM dbo.MbfRoleUser WHERE RoleID IN (SELECT ID FROM dbo.MbfRole
> > WHERE Description LIKE "Report Recipient: %")
> >
> > /*Delete Report Recipient Roles not in MbfUser*/
> > DELETE FROM dbo.MbfRole WHERE Description LIKE "Report Recipient: %" AND
> > Name IN (SELECT Name from dbo.MbfUser)
> >
> > /*Delete MbfDynamicViewSecurity records no longer in MbfRole*/
> > DELETE FROM dbo.MbfDynamicViewSecurity WHERE RightsID NOT IN (SELECT ID FROM
> > dbo.MbfRole)
> >
> > /*Delete MbfWSSCSGroupPart records no longer in MbfRole*/
> > DELETE FROM dbo.MbfWSSCSGroupPart WHERE BusinessPortalID NOT IN (SELECT ID
> > FROM dbo.MbfRole)
> >
> > /*Insert into the MbfRole table records from the MbfUser table that are not
> > in the MbfRole table (excepting Administrator -- that's already in there)*/
> > INSERT INTO dbo.MbfRole (ID, Name, Description, ConstituentType, TypeID)
> > SELECT NEWID(), Name, "Report Recipient: " + Name,
> > "Microsoft.Solomon.SystemManager.SolomonUser", 1 FROM dbo.MbfUser WHERE Name
> > <> 'Administrator' AND Name NOT IN (SELECT Name FROM dbo.MbfRole)
> >
> > /*Give all the new users (those not in the MbfDynamicViewSecurity table)
> > Report Request Data Permissions*/
> > INSERT INTO dbo.MbfDynamicViewSecurity (DynamicViewID, RightsID,
> > Permissions) SELECT 'A44CD000-302E-447A-8D70-D505C50C3E3A', dbo.MbfRole.ID,
> > 36 FROM dbo.MbfRole WHERE dbo.MbfRole.ID NOT IN (SELECT RightsID FROM
> > dbo.MbfDynamicViewSecurity)
> >
> > /*Insert into the MbfWSSCSGroupPart table records from the MbfUser table
> > that aren't in the MbfWSSCSGroupPart table*/
> > INSERT INTO dbo.MbfWSSCSGroupPart (ID, BusinessPortalID, Name, TypeID)
> > SELECT NEWID(), ID, Name, 0 FROM dbo.MbfRole WHERE Name <> 'Administrator'
> > AND ID NOT IN (SELECT BusinessPortalID FROM dbo.MbfWSSCSGroupPart)
> >
> > /*Insert into the MbfRoleUser table records from the MbfUser table
> > (excepting the Administrator)*/
> > INSERT INTO dbo.MbfRoleUser (UserID, RoleID, ContainerID, Container, TypeID)
> > SELECT dbo.MbfUser.ID, dbo.MbfRole.ID, 
> > '200D3CE8-F6E0-4113-8D63-5339EE5D88AA',
> > '<entityKey><Microsoft.Solomon.Common.Company.Company ID="EPRIZE"
> > /></entityKey>', 1 FROM dbo.MbfRole INNER JOIN dbo.MbfUser ON
> > dbo.MbfRole.Name = dbo.MbfUser.Name WHERE dbo.MbfRole.Name <> 'Administrator'
> >
> > Thanks for your help

"trufaux" wrote:

> Why do I need to reconnect to the database?  Is something about my trigger
> disconnecting to the database?  Can I reconnect to the database
> programmatically through the trigger?  IISReset is very time consuming each
> time.
>
> Thanks in advance,
>
>
> trufaux
>
> "John Bell" wrote:
>
> > Hi
> >
> > I assume that it is not IISReset that is making this work, but the
> > re-connection to the database.
> >
> > John
> >
> > "trufaux" wrote:
> >
> > > What follows is a trigger I wrote to mirror the MbfUser to the MbfRole table
> > > in my Business Portal database.  I send a report to one of these roles and
> > > for some reason it doesn't show until after I do an IISReset on my server.
> > >
> > > /*Because of a constraint on MbfRoleUser, MbfRoleUser must be cleared out
> > > before MbfRole.*/
> > >
> > > /*Delete MbfRoleUser records with corresponding MbfRole records with
> > > Descriptions like "Report Recipient..."*/
> > > DELETE FROM dbo.MbfRoleUser WHERE RoleID IN (SELECT ID FROM dbo.MbfRole
> > > WHERE Description LIKE "Report Recipient: %")
> > >
> > > /*Delete Report Recipient Roles not in MbfUser*/
> > > DELETE FROM dbo.MbfRole WHERE Description LIKE "Report Recipient: %" AND
> > > Name IN (SELECT Name from dbo.MbfUser)
> > >
> > > /*Delete MbfDynamicViewSecurity records no longer in MbfRole*/
> > > DELETE FROM dbo.MbfDynamicViewSecurity WHERE RightsID NOT IN (SELECT ID FROM
> > > dbo.MbfRole)
> > >
> > > /*Delete MbfWSSCSGroupPart records no longer in MbfRole*/
> > > DELETE FROM dbo.MbfWSSCSGroupPart WHERE BusinessPortalID NOT IN (SELECT ID
> > > FROM dbo.MbfRole)
> > >
> > > /*Insert into the MbfRole table records from the MbfUser table that are not
> > > in the MbfRole table (excepting Administrator -- that's already in there)*/
> > > INSERT INTO dbo.MbfRole (ID, Name, Description, ConstituentType, TypeID)
> > > SELECT NEWID(), Name, "Report Recipient: " + Name,
> > > "Microsoft.Solomon.SystemManager.SolomonUser", 1 FROM dbo.MbfUser WHERE Name
> > > <> 'Administrator' AND Name NOT IN (SELECT Name FROM dbo.MbfRole)
> > >
> > > /*Give all the new users (those not in the MbfDynamicViewSecurity table)
> > > Report Request Data Permissions*/
> > > INSERT INTO dbo.MbfDynamicViewSecurity (DynamicViewID, RightsID,
> > > Permissions) SELECT 'A44CD000-302E-447A-8D70-D505C50C3E3A', dbo.MbfRole.ID,
> > > 36 FROM dbo.MbfRole WHERE dbo.MbfRole.ID NOT IN (SELECT RightsID FROM
> > > dbo.MbfDynamicViewSecurity)
> > >
> > > /*Insert into the MbfWSSCSGroupPart table records from the MbfUser table
> > > that aren't in the MbfWSSCSGroupPart table*/
> > > INSERT INTO dbo.MbfWSSCSGroupPart (ID, BusinessPortalID, Name, TypeID)
> > > SELECT NEWID(), ID, Name, 0 FROM dbo.MbfRole WHERE Name <> 'Administrator'
> > > AND ID NOT IN (SELECT BusinessPortalID FROM dbo.MbfWSSCSGroupPart)
> > >
> > > /*Insert into the MbfRoleUser table records from the MbfUser table
> > > (excepting the Administrator)*/
> > > INSERT INTO dbo.MbfRoleUser (UserID, RoleID, ContainerID, Container, TypeID)
> > > SELECT dbo.MbfUser.ID, dbo.MbfRole.ID, 
> > > '200D3CE8-F6E0-4113-8D63-5339EE5D88AA',
> > > '<entityKey><Microsoft.Solomon.Common.Company.Company ID="EPRIZE"
> > > /></entityKey>', 1 FROM dbo.MbfRole INNER JOIN dbo.MbfUser ON
> > > dbo.MbfRole.Name = dbo.MbfUser.Name WHERE dbo.MbfRole.Name <> 'Administrator'
> > >
> > > Thanks for your help

"trufaux" wrote:

> What follows is a trigger I wrote to mirror the MbfUser to the MbfRole table
> in my Business Portal database.  I send a report to one of these roles and
> for some reason it doesn't show until after I do an IISReset on my server.
>
> /*Because of a constraint on MbfRoleUser, MbfRoleUser must be cleared out
> before MbfRole.*/
>
> /*Delete MbfRoleUser records with corresponding MbfRole records with
> Descriptions like "Report Recipient..."*/
> DELETE FROM dbo.MbfRoleUser WHERE RoleID IN (SELECT ID FROM dbo.MbfRole
> WHERE Description LIKE "Report Recipient: %")
>
> /*Delete Report Recipient Roles not in MbfUser*/
> DELETE FROM dbo.MbfRole WHERE Description LIKE "Report Recipient: %" AND
> Name IN (SELECT Name from dbo.MbfUser)
>
> /*Delete MbfDynamicViewSecurity records no longer in MbfRole*/
> DELETE FROM dbo.MbfDynamicViewSecurity WHERE RightsID NOT IN (SELECT ID FROM
> dbo.MbfRole)
>
> /*Delete MbfWSSCSGroupPart records no longer in MbfRole*/
> DELETE FROM dbo.MbfWSSCSGroupPart WHERE BusinessPortalID NOT IN (SELECT ID
> FROM dbo.MbfRole)
>
> /*Insert into the MbfRole table records from the MbfUser table that are not
> in the MbfRole table (excepting Administrator -- that's already in there)*/
> INSERT INTO dbo.MbfRole (ID, Name, Description, ConstituentType, TypeID)
> SELECT NEWID(), Name, "Report Recipient: " + Name,
> "Microsoft.Solomon.SystemManager.SolomonUser", 1 FROM dbo.MbfUser WHERE Name
> <> 'Administrator' AND Name NOT IN (SELECT Name FROM dbo.MbfRole)
>
> /*Give all the new users (those not in the MbfDynamicViewSecurity table)
> Report Request Data Permissions*/
> INSERT INTO dbo.MbfDynamicViewSecurity (DynamicViewID, RightsID,
> Permissions) SELECT 'A44CD000-302E-447A-8D70-D505C50C3E3A', dbo.MbfRole.ID,
> 36 FROM dbo.MbfRole WHERE dbo.MbfRole.ID NOT IN (SELECT RightsID FROM
> dbo.MbfDynamicViewSecurity)
>
> /*Insert into the MbfWSSCSGroupPart table records from the MbfUser table
> that aren't in the MbfWSSCSGroupPart table*/
> INSERT INTO dbo.MbfWSSCSGroupPart (ID, BusinessPortalID, Name, TypeID)
> SELECT NEWID(), ID, Name, 0 FROM dbo.MbfRole WHERE Name <> 'Administrator'
> AND ID NOT IN (SELECT BusinessPortalID FROM dbo.MbfWSSCSGroupPart)
>
> /*Insert into the MbfRoleUser table records from the MbfUser table
> (excepting the Administrator)*/
> INSERT INTO dbo.MbfRoleUser (UserID, RoleID, ContainerID, Container, TypeID)
> SELECT dbo.MbfUser.ID, dbo.MbfRole.ID, 
> '200D3CE8-F6E0-4113-8D63-5339EE5D88AA',
> '<entityKey><Microsoft.Solomon.Common.Company.Company ID="EPRIZE"
> /></entityKey>', 1 FROM dbo.MbfRole INNER JOIN dbo.MbfUser ON
> dbo.MbfRole.Name = dbo.MbfUser.Name WHERE dbo.MbfRole.Name <> 'Administrator'
>
> Thanks for your help