Here is an example for what I did to setup TFS 2010 cross-project user group permission by changing its database

1. In Team Explorer, for each project :

a. “Team Project Settings”->”Group Membership”

b. Create an assigned QA team (ie, QA-xxxx, QA-yyyy, etc.) so their members can be showing up in Urban Turtle GUI

2. In SSMS, manipulate the visibility of those user groups

a. List all QA groups, get their sids from tfs_configuration.dbo.tbl_gss_groups, and SAVE them as we will need to reverse them back

SELECT [sid]

,[special_type]

,[display_name]

,[description]

,[pid]

,[restricted_view]

,[scope_local]

,[tf_id]

FROM [Tfs_Configuration].[dbo].[tbl_gss_groups]

WHERE display_name like ‘QA-%’

ORDER BY [pid]

b. Change them all to 5 (collection level) during the setup so we don’t have to go back and forth between projects

UPDATE [Tfs_Configuration].[dbo].[tbl_gss_groups]

SET pid = 5

WHERE display_name like ‘QA-%’

3. In Team Explorer, create collection level groups for different QA desired permission

a. QA-CRUD

* consist of all (with same desired CRUD permission) QA assign groups from different projects

* will not show up in Urban Turtle

b. QA-CRU

* consist of all (with same desired CRU permission) QA assign groups from different projects

* will not show up in Urban Turtle

c. QA-R

* consist of all (with same desired R permission) QA assign groups from different projects

* will not show up in Urban Turtle

4. In Team Explorer, assign permission for those QA user groups under “Team Project Settings”->Security for each team project

a. Add the QA-CRUD group

b. Allow the following permission to the group

* Create test runs

* Delete test runs

* Manage test configurations

* Manage test environments

* View project-level information

* View test runs

c. Do not deny any permission so we have the flexibility to manage them some where else

d. Add the QA-CRU group

e. Allow the following permission to the group

* Create test runs

* Manage test configurations

* Manage test environments

* View project-level information

* View test runs

f. Do not deny any permission so we have the flexibility to manage them some where else

g. Add the QA-R group

h. Allow the following permission to the group

* View project-level information

* View test runs

i. Do not deny any permission so we have the flexibility to manage them some where else

5. assign permission for those QA user groups under “Team Project Settings”->”Areas and Iterations”

a. Common areas (top level)

* Add QA-CRUD & QA-CRU group

* Allow the following permission to the group

– Edit work items in this node

– Manage test plans

– View this node

– View work items in this node

* Add QA-R group

* Allow the following permission to the group

– View this node

– View work items in this node

b. Assigned areas ( ie, xxx, yyy)

* Add specific assigned groups (ie, QA-xxx, xxx-yyy-Lead)

* Allow the following permission to the group

– Edit work items in this node

– Manage test plans

– View this node

– View work items in this node

6. Change MTM items (test plans and cases) to the specific area

a. Test Plans

* Query all related test plan records

SELECT * FROM tfs_DefaultCollection.dbo.tbl_Plan

WHERE NAME = ‘xxx – yyyyyyyyy’

* Change the AreaId to controlled area (xxxxxxxxxxxx/yyy = 109, from tfs_DefaultCollection.dbo.tbl_Area)

UPDATE tfs_DefaultCollection.dbo.tbl_Plan

SET AreaId = 109

FROM tfs_DefaultCollection.dbo.tbl_Plan p

WHERE p.NAME = ‘xxx – yyyyyyyy’

b. Test Cases

* Query all related test plan records

SELECT p.PlanId, p.Name, s.PlanId, s.SuiteId, s.Title, se.SuiteId, se.TestCaseId, w.ID, w.Title, w.AreaID

FROM tfs_DefaultCollection.dbo.tbl_Plan p

JOIN tfs_DefaultCollection.dbo.tbl_Suite s

ON s.PlanId = p.PlanId

JOIN tfs_DefaultCollection.dbo.tbl_SuiteEntry se

ON se.SuiteId = s.SuiteId

JOIN tfs_DefaultCollection.dbo.WorkItemsLatest w

ON w.ID = se.TestCaseId

WHERE p.Name = ‘xxx – yyyyyyyy’

AND w.AreaID = 523 –(xxxxxxxxxxxx)

AND se.ChildSuiteId = 0

* Change the AreaId to controlled area (xxxxxxxxxxxx/yyy= 554, from tfs_DefaultCollection.dbo.xxTree)

UPDATE tfs_DefaultCollection.dbo.WorkItemsLatest

SET AreaId = 554

FROM tfs_DefaultCollection.dbo.WorkItemsLatest w

JOIN tfs_DefaultCollection.dbo.tbl_SuiteEntry se

ON w.ID = se.TestCaseId

JOIN tfs_DefaultCollection.dbo.tbl_Suite s

ON se.SuiteId = s.SuiteId

JOIN tfs_DefaultCollection.dbo.tbl_Plan p

ON s.PlanId = p.PlanId

WHERE p.Name = ‘xxx – yyyyyyyy’

AND w.AreaID = 523 –(xxxxxxxxxxxx)

AND se.ChildSuiteId = 0

UPDATE tfs_DefaultCollection.dbo.WorkItemsAre

SET AreaId = 554

select *

FROM tfs_DefaultCollection.dbo.WorkItemsAre w

JOIN tfs_DefaultCollection.dbo.tbl_SuiteEntry se

ON w.ID = se.TestCaseId

JOIN tfs_DefaultCollection.dbo.tbl_Suite s

ON se.SuiteId = s.SuiteId

JOIN tfs_DefaultCollection.dbo.tbl_Plan p

ON s.PlanId = p.PlanId

WHERE p.Name = ‘xxx- yyyyyyyy’

AND w.AreaID = 523 –(xxxxxxxxxxxx)

AND se.ChildSuiteId = 0

7. In SSMS, reverse back the visibility of those user groups

a. List all QA groups, get their sids from tfs_configuration.dbo.tbl_gss_groups

SELECT [sid]

,[special_type]

,[display_name]

,[description]

,[pid]

,[restricted_view]

,[scope_local]

,[tf_id]

FROM [Tfs_Configuration].[dbo].[tbl_gss_groups]

WHERE display_name like ‘QA-%’

AND display_name NOT IN (‘QA-CRUD’, ‘QA-CRU’, ‘QA-R’)

ORDER BY pid

b. Change them all back (did I mention please save them somewhere before we change them?)

SELECT * FROM [Tfs_Configuration].[dbo].[tbl_gss_groups]

WHERE display_name = ‘QA-OPM’

UPDATE [Tfs_Configuration].[dbo].[tbl_gss_groups]

SET pid = 16

WHERE display_name = ‘QA-OPM’

SELECT * FROM [Tfs_Configuration].[dbo].[tbl_gss_groups]

WHERE display_name = ‘QA-OPM’

….. for each user group …..

8. Verify by QA for the access permission, should not change anything at all

9. Remove previous user group settings for all QA members

10. Verify the permission still in place

Advertisements