What are these :N (:1, :2, etc.) entities in my permissions?

In this thread on the forums some users are seeing permissions on their team project folders that include the expected groups, but the groups have :N (:1, :2, etc.) after their names.  What's going on here?

One of the things we do in Team Foundation Version Control is keep identities around even after the user/group has been deleted.  Why would we keep deleted identities around?  Because otherwise we wouldn't have very much useful to show otherwise.  For instance, if I'm looking at a changeset that a developer that has since left the company checked in, I want to still see that user listed.  I don't want something that just says [deleted user] or whatever - that's not very helpful.

However, you may be used to TFVC's "deletion id's" - where the moment an item in version control gets deleted, we give it a deletion id.  The :N number here isn't quite the same thing.  Once the identity gets deleted, we don't change its name at all in TFVC.  What is happening here is that the moment we (TFVC) learn about a new identity with the same name, we take the previous identity and add the :N number to it.  The N part is just an increasing number across TFVC (Readers:3 isn't the third deleted Readers, for instance).

One of the odd side effects of that, though, is that if someone deletes a user/group/whatever, we still allow changing permissions to that group even though they're deleted.  For instance, if you had a team project called proj and it had a group called group (creative, i know), then you could "tfssecurity /gd" to delete [proj]\group and turn around and still "tf perm /group:[proj]\group" to assign that group permissions.  "tf perm" on a folder that had permissions assigned to that group would still list them.

Unfortunately, as you can see in that forum thread, we had 2 bugs in Beta3 (fixed in December CTP and current bits) that caused a very painful situation.

1. At team project creation time, we need to clear the permissions at a given path.  For instance, if you're creating team project "foo" for the second time, then as we're creating that folder in version control, we need to clear out the permissions for path $/foo.  That wasn't happening in Beta3 unfortunately.  Because of this, since in this case the "foo" team project had been created and deleted before, we still had old permissions granted at $/foo to groups that are since deleted.  Since we've gone and created the "foo" team project again, groups by that same name have since been created, so the permissions explicitly granted on $/foo include those grants to the ":N" groups (Project Administrators:1, Contributors:2, etc.)
2. When an item has permissions that include these deleted (":N") groups (again, this is Beta3 and this bug has since been fixed), those permissions "mask" any subsequently added permissions.  Hence, the second time we created team project "foo", we added permissions for the newly created groups (Contributors, Readers, etc.), and those rows actually got added to the database just fine.  However, both "tf perm" and actual permission evaluation didn't include those new rows - the only ones you would see listed out were the ":N" (deleted) groups.  Note that this was an item-level issue - global permissions didn't have this "deleted masks non-deleted" issue in Beta3.

These two bugs worked in concert to create a painful situation for Beta3 customers that created a team project with the same name as a previous one - they were stuck.  They couldn't use "tf perm" (or the UI) to remove the permissions to these deleted groups, and with the permissions to those deleted groups around, all other permissions were being masked.

There are a few different workarounds available:

1. Grant item permissions to the groups ([foo]\Contributors, etc.) at the root ($/) level instead, and let the fact that the team project inherits permissions from $/ get those permissions in effect.  This has the unfortunate side effect where those groups then have those permissions in all team projects (by default).  For a low number of team projects, or situations where the same people will be in those groups on all the team projects, this may be acceptable.  Another mitigation path may be to turn off inherit on the other team projects so they don't inherit those permissions from $/
2. Wait until our RC is out (which WILL have the Go-Live license) and upgrade to that.  Since we want as many people as possible helping to bang on our RC to make sure it's rock-solid, this would be great :)  The relevant bug fixes were around the time we released the December CTP (which does NOT have the Go-Live license), so I think the December CTP bits may still have this issue, but I haven't had a chance to try it on that build yet to say for sure. Since it's not Go-Live, it's not generally useful to most groups on Beta3 to consider upgrading to those bits.
3. [least desirable] clear out the tbl_Permission rows that include the deleted groups.  Once those groups are out of the way, the ones assigned at PCW time (and any assigned afterwards) will start taking effect.  The usual disclaimers apply for such a hack: that this could screw up your database (so if you do this, please back up first!) and isn't officially supported.

If, despite the warnings, you still want to take the SQL route and remove the offending rows, here's the query (well, statement, as it's DML) you would need to run in the TfsVersionControl database.  Make sure you "iisreset" on the application tier after making this change, as we cache permissions information there and the restart will force it to re-read permissions information from the database.

DELETE FROM tbl_Permission
WHERE (IdentityId IN
(SELECT IdentityId
FROM tbl_Identity
WHERE (UniqueUserId IS NOT NULL)))

[Edit]: this SQL fixed has worked for myself in testing on 2 different machines and at least one customer.