The Problem

Let’s start by underlining the principal that people should not have more permissions to a system than they need. Furthermore, no one should have system permissions that can reasonably be expected to exceed their competence. Mystifyingly, this commonplace seems to have been lost by the TFS product team. Herewith is the fix.

Out-of-the-box, TFS Projects come with four permission levels (roles):

  • Reader
  • Contributor
  • (Build Administrator)
  • Project Administrator

Readers, of course, have read-only access to the TFS Project. This is essentially the minimum permission set for any type of access to the Project.

The Contributor role is appropriately scoped so that the typical developer/tester has the permissions he or she needs to contribute their work to TFS. Contributors do not have permission to substantially change the Project.

The Build Administrator role is an example of an appropriately graduated role. It grants members additional permissions to manage builds and build definitions, and nothing more.

However, Teams cannot use TFS with only Contributor (or Build Administrator) permissions. Routine use requires that some members must be able to manage iterations (and area paths), for example. Or merge and branch code. These users must be given permissions higher than Contributor, and that means Project Administrator.

The problem is that the Project Administrator role can modify workitem type definitions, manage states, transitions, and even delete the Project altogether.

When you give the PM assigned to managing iterations (for example) enough permissions to do this, he or she should simply not have the ability to unintentionally do this much damage!

The Solution

By creating a Power Users role, we are able to create a permission set graduated between Contributor and Project Administrator. This role has the ability to manage routine processes, like Iterations, Area Paths, Branching, etc. This role cannot manage security, nor change the template configuration.

Creating a role requires running a series of commands on the TFSSecurity.exe tool. It is not straightforward to get the object references for this tool, and these are different for every Project. (Also, because TFS is quite happy to grant permissions on objects that don’t exist, it is not obvious when the references are wrong). Ultimately, the easiest way to collect the names, paths, or IDs was to query the objects out of the SQL backend. Note that this SQL script only reads from the database; it does no unsupported writes.

Essentially, the strategy is to query the database for the permissions belonging to the Project Administrators group, remove the permissions we don’t want, then reapply those permissions for the Power Users group using the TFSSecurity command line tool.

The general steps are:

  1. Run the provided SQL against the TFS Project Collection database
  2. Copy the query results into a command window and run it against the TFS instance
  3. Manually fix Tagging.

1. Running the SQL

Open a SQL query tool, like SQL Management Studio, and connect to the database that corresponds to the Project Collection containing your Project.

Paste the following SQL into your SQL editing window (but don’t run it just yet):

--GENERATE "CREATE 'POWER USERS'" SCRIPT FOR THE SPECIFIED PROJECT
DECLARE @ProjectName sysname, @TFSCollection sysname;
SET @ProjectName = N'ESB' --UPDATE THIS PROJECT NAME
SET @TFSCollection = N'http://TFSSERVERNAME:8080/MyCollection' --UPDATE THIS TFS COLLECTION URL, AS NEEDED
 
DECLARE @SourceGroupName sysname, @NewGroupName sysname;
SET @SourceGroupName = N'Project Administrators'
SET @NewGroupName = N'Power Users'
DECLARE @ProjectGUID char(36);
SET @ProjectGUID = (SELECT TOP 1 SubString(ProjectUri, 37, 36) FROM dbo.tbl_Project WHERE ProjectName = @ProjectName);
--Create Security Group
SELECT N'TfsSecurity /gc "' + tbl_Project.ProjectUri + N'" "Power Users" "This group is for users who need to manage the TFS Project, but don''t need permission to modify the template or configuration." /collection:' + @TFSCollection As CommandLine
FROM tbl_Project WHERE ProjectName = @ProjectName
--Clone relvant permissions from "SourceGroupName"
UNION SELECT N'TfsSecurity /g+ "[' + @ProjectName + N']\' + @ProjectName + N' Team" "[' + @ProjectName + N']\' + @NewGroupName + N'" /collection:' + @TFSCollection
ORDER BY CommandLine DESC;
 
SELECT N'TfsSecurity /a+ "' + tbl_SecurityNamespace.Name + N'" ' + tbl_SecurityAccessControlEntry.SecurityToken + N' ' + tbl_SecurityAction.Name + N' "[' + @ProjectName + N']\' + @NewGroupName + N'" ALLOW /collection:' + @TFSCollection As CommandLine
FROM dbo.tbl_SecurityNamespace
       INNER JOIN dbo.tbl_SecurityAction
       ON tbl_SecurityAction.PartitionId = tbl_SecurityNamespace.PartitionId
              AND tbl_SecurityAction.NamespaceId = tbl_SecurityNamespace.NamespaceGuid
       INNER JOIN dbo.tbl_SecurityAccessControlEntry
       ON tbl_SecurityAccessControlEntry.PartitionId = tbl_SecurityNamespace.PartitionId
              AND tbl_SecurityAccessControlEntry.NamespaceGuid = tbl_SecurityNamespace.NamespaceGuid
              AND (tbl_SecurityAccessControlEntry.AllowPermission & tbl_SecurityAction.Bit) = tbl_SecurityAction.Bit
       INNER JOIN dbo.ADObjects
       ON ADObjects.PartitionId = tbl_SecurityAccessControlEntry.PartitionId
              AND ADObjects.TeamFoundationId = tbl_SecurityAccessControlEntry.TeamFoundationId
WHERE ADObjects.DomainName = @ProjectGUID
       AND ADObjects.SamAccountName = @SourceGroupName
       AND tbl_SecurityNamespace.Name NOT IN (N'Build', N'Tagging', N'Identity', N'TeamLabSecurity', N'WorkItemTrackingProvision', --expected and blocked
N'WorkItemTrackingAdministration', N'Registry', N'Job', N'StrongBox', N'Server', N'Collection', N'BuildAdministration', N'VersionControlPrivileges', N'Workspaces', N'ProjectServerAdministration', N'EventSubscription', N'CSS', N'Discussion Threads' --not expected

              )

       AND tbl_SecurityAction.Name <> 'GENERIC_WRITE'

ORDER BY tbl_SecurityNamespace.Name, tbl_SecurityAction.Name, ADObjects.DomainName, ADObjects.SamAccountName;

--Test management permissions

SELECT N'TfsSecurity /a+ Project $PROJECT:vstfs:///Classification/TeamProject/' +

@ProjectGUID + N': MANAGE_TEST_CONFIGURATIONS "[' + @ProjectName + N']\' +

@NewGroupName + N'" ALLOW /collection:' + @TFSCollection As CommandLine

UNION SELECT N'TfsSecurity /a+ Project $PROJECT:vstfs:///Classification/TeamProject/' +

@ProjectGUID + N': MANAGE_TEST_ENVIRONMENTS "[' + @ProjectName + N']\' +

@NewGroupName + N'" ALLOW /collection:' + @TFSCollection

UNION SELECT N'TfsSecurity /a+ Project $PROJECT:vstfs:///Classification/TeamProject/' + @ProjectGUID + N': PUBLISH_TEST_RESULTS "[' + @ProjectName + N']\' + @NewGroupName + N'" ALLOW /collection:' + @TFSCollection

UNION SELECT N'TfsSecurity /a+ Project $PROJECT:vstfs:///Classification/TeamProject/' + @ProjectGUID + N': VIEW_TEST_RESULTS "[' + @ProjectName + N']\' + @NewGroupName + N'" ALLOW /collection:' + @TFSCollection

UNION SELECT N'TfsSecurity /a+ Project $PROJECT:vstfs:///Classification/TeamProject/' + @ProjectGUID + N': DELETE_TEST_RESULTS "[' + @ProjectName + N']\' + @NewGroupName + N'" ALLOW /collection:' + @TFSCollection

--Grant permission to manage Area Paths

UNION SELECT N'TfsSecurity /a+ CSS ' + tbl_Area.AreaUri + N': CREATE_CHILDREN "[' + @ProjectName + N']\' + @NewGroupName + N'" ALLOW /collection:' + @TFSCollection

FROM dbo.tbl_Area

WHERE tbl_Area.ProjectUri = 'vstfs:///Classification/TeamProject/' + @ProjectGUID

       AND tbl_Area.AreaPath = Replace(@ProjectName, N'_', N'>')

UNION SELECT N'TfsSecurity /a+ CSS ' + tbl_Area.AreaUri + N': GENERIC_WRITE "[' + @ProjectName + N']\' + @NewGroupName + N'" ALLOW /collection:' + @TFSCollection

FROM dbo.tbl_Area

WHERE tbl_Area.ProjectUri = 'vstfs:///Classification/TeamProject/' + @ProjectGUID

       AND tbl_Area.AreaPath = Replace(@ProjectName, N'_', N'>')

--Grant permission to manage Iterations

UNION SELECT N'TfsSecurity /a+ Iteration ' + tbl_Iteration.IterationUri + N': CREATE_CHILDREN "[' + @ProjectName + N']\' + @NewGroupName + N'" ALLOW /collection:' + @TFSCollection

FROM dbo.tbl_Iteration

WHERE tbl_Iteration.ProjectUri = 'vstfs:///Classification/TeamProject/' + @ProjectGUID

       AND tbl_Iteration.Iteration = Replace(@ProjectName, N'_', N'>')

UNION SELECT N'TfsSecurity /a+ Iteration ' + tbl_Iteration.IterationUri + N': GENERIC_WRITE "[' + @ProjectName + N']\' + @NewGroupName + N'" ALLOW /collection:' + @TFSCollection

FROM dbo.tbl_Iteration

WHERE tbl_Iteration.ProjectUri = 'vstfs:///Classification/TeamProject/' + @ProjectGUID

       AND tbl_Iteration.Iteration = Replace(@ProjectName, N'_', N'>')

--Add Power Users to Team Administrators

UNION SELECT N'TfsSecurity /a+ "Identity" ' + @ProjectGUID + N'\' + Cast(tbl_TeamConfigurationTeamFields.TeamId as char(36)) + '\ Read "[' + @ProjectName + N']\' + @NewGroupName + N'" ALLOW /collection:' + @TFSCollection

FROM dbo.tbl_TeamConfigurationTeamFields

WHERE Left(tbl_TeamConfigurationTeamFields.TeamFieldValue, Len(@ProjectName)) = @ProjectName

       AND tbl_TeamConfigurationTeamFields.[Order] = 0

UNION SELECT N'TfsSecurity /a+ "Identity" ' + @ProjectGUID + N'\' + Cast(tbl_TeamConfigurationTeamFields.TeamId as char(36)) + '\ Write "[' + @ProjectName + N']\' + @NewGroupName + N'" ALLOW /collection:' + @TFSCollection

FROM dbo.tbl_TeamConfigurationTeamFields

WHERE Left(tbl_TeamConfigurationTeamFields.TeamFieldValue, Len(@ProjectName)) = @ProjectName

       AND tbl_TeamConfigurationTeamFields.[Order] = 0

UNION SELECT N'TfsSecurity /a+ "Identity" ' + @ProjectGUID + N'\' + Cast(tbl_TeamConfigurationTeamFields.TeamId as char(36)) + '\ Delete "[' + @ProjectName + N']\' + @NewGroupName + N'" ALLOW /collection:' + @TFSCollection

FROM dbo.tbl_TeamConfigurationTeamFields

WHERE Left(tbl_TeamConfigurationTeamFields.TeamFieldValue, Len(@ProjectName)) = @ProjectName

       AND tbl_TeamConfigurationTeamFields.[Order] = 0

UNION SELECT N'TfsSecurity /a+ "Identity" ' + @ProjectGUID + N'\' + Cast(tbl_TeamConfigurationTeamFields.TeamId as char(36)) + '\ ManageMembership "[' + @ProjectName + N']\' + @NewGroupName + N'" ALLOW /collection:' + @TFSCollection

FROM dbo.tbl_TeamConfigurationTeamFields

WHERE Left(tbl_TeamConfigurationTeamFields.TeamFieldValue, Len(@ProjectName)) = @ProjectName

       AND tbl_TeamConfigurationTeamFields.[Order] = 0

 

SELECT N'TfsSecurity /gc "' + tbl_Project.ProjectUri + N'" "Test Agent Accounts" "This group gives the Test Service accounts the ability to update a Test Suite within this Project. This allows the TCM command to add new tests if they are discovered in its test executable." /collection:' + @TFSCollection As CommandLine

FROM tbl_Project

WHERE ProjectName = @ProjectName

UNION SELECT N'TfsSecurity /g+ "[' + @ProjectName + N']\Test Agent Accounts" n:CORP\s-TfsTestAgent /collection:' + @TFSCollection

UNION SELECT N'TfsSecurity /g+ "[' + @ProjectName + N']\Test Agent Accounts" n:CORP\s-TfsTestCtrl /collection:' + @TFSCollection

UNION SELECT N'TfsSecurity /a+ Project $PROJECT:vstfs:///Classification/TeamProject/' + @ProjectGUID + N': MANAGE_TEST_CONFIGURATIONS "[' + @ProjectName + N']\' + @NewGroupName + N'" ALLOW /collection:' + @TFSCollection As CommandLine

ORDER BY CommandLine DESC;

 

On the second and third lines of the SQL code, change the variable @ProjectName to the name of your Project and @TFSCollection to the URL of the corresponding Project collection.

Run the SQL statement. You should have several distinct outputs in your results. These are the series of commands that you need to run to create the Project Power Users group.

2. Running the Commands

Now that you have the command sequence, you need to open the command window (you can right-click on start and select Command Prompt). The computer running the command sequence does not have to be a TFS server, any machine with a recent version of Visual Studio and access to TFS will work. The command window does not need to be opened with local Administrator permissions. The desktop account should be a TFS Administrator.

In the opened command windows, change the path to reference the TFS command line tools. Depending upon the version of Visual Studio installed this could be:

C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE
C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE

When the path is set, paste the command sequence into the command window. (I suggest doing this gradually, or even one line at a time, when you’re just getting started).

The commands should report success and create the new Power Users group in TFS.

3. Fix Tagging

TFS has an unfortunate default of allowing all Contributors to create new workitem Tags. This means that nearly anyone can create a new Tag. That sounds great until you realize that redundant and similar Tags cannot removed, and this can have a very negative impact on the quality of your queries. For example, if one user create a Tag called “.NET” and another user creates a Tag called “.NET Framework”, searches for one will not return results that are Tagged with the other. Afterward, users will need to consistently know whether to use “.NET” or “.NET Framework” as the appropriate workitem tag.

Clearly, it is important to ensure some type of agreement on workitem Tags. We decided early that only a few individuals on each team should be able to create Tags. These users happen to be Power Users.

Bizarrely, there is no automated (command line) way to add Tagging permissions to Power Users, and remove it from Contributors. All Tagging commands fail. My theory is that the documented command arguments to set Tagging permissions is misinterpreted by the command-line pre-processor and rendered invalid (as of TFS 2013, I've not tried in TFS 2015). This means that you can only do this through the TFS web interface.

  1. Open the Project in your web browser and go to the Admin interface (click on the gear icon in the upper right, or just add "/_admin" to the URL)
  2. Click in the Security tab in Project Administration page
  3. Select the Contributors TFS group in the left-hand list
  4. In the Permissions window, click on option after “Create tag definition” until it toggles to Not set
  5. Click on the Save changes button
  6. Select the new Power Users TFS group in the left-hand list
  7. In the Permissions window, click on option after “Create tag definition” until it toggles to Allow
  8. Click on the Save changes button

That’s it. Power users should have the ability to manage Tags, Iterations, and generally keep the Project in shape without permissions to modify workitem definitions, states, or other dangerous things.