Project Server PSI WCF bug – WSEC_CAT_UID and WSEC_GRP_UID

At one of my customers we are automating the project and project site creation for Project Server 2010.  As part of that, we wanted the proper permissions to be setup for each resource/user.  This includes making them in the "Project Manager" group and the "My Projects" and "My Resources" categories.  The code wasn't very straight forward, but I got most of it working (creating the resource and making a resource a user).  The part that doesn't work is the group and categories assignment.  Here is the code:

SvcSecurity.SecurityGroupsDataSet sgDataSet = new SvcSecurity.SecurityGroupsDataSet();
//Get the security group
SvcSecurity.SecurityGroupsDataSet sglist = securityClient.ReadGroupList();
SvcSecurity.
SecurityGroupsDataSet.SecurityGroupsRow groupDs = null;
foreach (SvcSecurity.SecurityGroupsDataSet.SecurityGroupsRow sg in sglist.SecurityGroups)
{
if (sg.WSEC_GRP_NAME == groupName)
{
groupDs = sg;|
}
}

SvcSecurity.SecurityGroupsDataSet ds = new SvcSecurity.SecurityGroupsDataSet();
// Specify which users belong to the new group.

SvcSecurity.SecurityGroupsDataSet ds = new SvcSecurity.SecurityGroupsDataSet();
// Specify which users belong to the new group.

SvcSecurity.SecurityGroupsDataSet.GroupMembersRow groupMembersRow = ds.GroupMembers.NewGroupMembersRow();
groupMembersRow.WSEC_GRP_UID = groupDs.WSEC_GRP_UID;
// Add the GUID of the resource to the group.
groupMembersRow.RES_UID = NewResGuid;
ds.GroupMembers.AddGroupMembersRow(groupMembersRow);
securityClient.SetGroups(ds);

SecurityGroupsDataSet.GroupMembersRow groupMembersRow = ds.GroupMembers.NewGroupMembersRow();
groupMembersRow.WSEC_GRP_UID = groupDs.WSEC_GRP_UID;
// Add the GUID of the resource to the group.
groupMembersRow.RES_UID = NewResGuid;
ds.GroupMembers.AddGroupMembersRow(groupMembersRow);
securityClient.SetGroups(ds);

The problem lies in the finding of the group.  The DataSet that is returned returns the WSEC_GRP_UID.  This particular column is NOT what the following AddGroupMembersRow call wants!  It actually is looking for a second GUID in the database called WSEC_GRP_GUID.  This is not returned in the PSI call and therefore, you have ZERO chance of successfully going through the PSI apis to get the id.  You end up having to query the database directly…yuk.

Anyone that develops PSI out there?  You really need to fix this…

Chris

ATTENTION: Database Leak in SharePoint 2010!!!

I have been doing a massive Oracle Portal migration, 3000+ pages of content, with a targeted subset of that in my dev envrionment at just over 3GB of content.  I noticed the other day that my development content database kept getting bigger and bigger.  Running a "dbcc checkdb" pointed me to the largest tables.  After clearing the Audit logs, EventLogs and everything else I could think of, then shrinking the database files, the databse size was still WAY bigger than what it was supposed to be (it should be 35GB, but it is currently 72GB).  The dbcc command pointed to the AllDocStreams table as the source of the problem.  I thought it possible the new web recycle bin may be the culprit, so I set out to find the root cause and prove to the community that "Redmond, we have a problem"

My first test was to create a web and then delete it over and over again.  That didn't change the row sizes in the database.  Next test was to do the same thing, only add a document to the doc library.  That didn't cause any leaks.  The last step was to enable versioning and to upload a document a few times, then delete the webs…VIOLA!  Found the leak!  Want to try this on your own…here is the PowerShell that proves that we have a big issue here:

function CheckRows($table, $conn)
{
$sql = "select count(*) as count from $table";
$cmd = new-object system.data.sqlclient.sqlcommand($sql, $conn)

$reader = $cmd.executereader()

while($reader.read())
{
$line = $reader["count"].tostring()
}

$reader.close()

"$table has " + $line + " rows"
}

function ReportRowCounts($cdb)
{

$connString = $cdb.LegacyDatabaseConnectionString

$conn = new-object system.data.sqlclient.sqlconnection
$conn.connectionstring = $connstring
$conn.open()

CheckRows "AllDocs" $conn
CheckRows "AllDocStreams" $conn
CheckRows "AllDocVersions" $conn

$conn.close()

}

function UploadFile($web)
{

$list = $web.lists["Shared Documents"]
$list.EnableModeration = $true
$list.EnableMinorVersions = $true
$list.contenttypesenabled = $true
$list.update()

$spFolder = $list.rootfolder
$spFileCollection = $spFolder.Files
$file = get-item "c:scriptsexport.zip"
$spfile = $spFileCollection.Add($file.name,$file.OpenRead(),$true)
$spfile.checkout()
$spfile.checkin("checkin")
$spfile.approve("approved")

}

$cdb = get-spcontentdatabase "WSS_Content_Contoso"
$web = get-spweb
http://www.contoso.com

ReportRowCounts($cdb)

#create a site
$subweb = $web.webs.add("Test1", "Test1", "", 1033, "STS#0", $false, $false);

ReportRowCounts($cdb)

$subweb.delete()

ReportRowCounts($cdb)

$subweb = $web.webs.add("Test1", "Test1", "", 1033, "STS#0", $false, $false);
UploadFile $subweb
UploadFile $subweb
UploadFile $subweb
UploadFile $subweb
UploadFile $subweb

ReportRowCounts($cdb)

$subweb.delete()

ReportRowCounts($cdb)

You will see the AllDocStreams table continues to get larger and larger.  This is the table that has your BLOBS!  The worst table to have a database leak!  From a high level, what does this mean?  It means if you turn on versioning, and say upload a 1Gb file and then make 5 changes to it, you will have 5GB of storage used in your database.  If you then delete the web, you will lose file pointers in the database and that 5GB will remain in the database forever and never get cleaned up.  Now, how likely is it that you are creating and deleting webs in production?  Not too many people will have a fancy process setup like this, but in Development and QA, you will.  I can't afford to keep restorting a "clean" copy of my development databases everytime it gets too big.

NOTE:  This bug shows up in all builds after SP1 (the web recycle build), my environment is the latest release build of 14.6112.5000.

Here's how to determine if you are having a problem and how BIG it is:

$global:count = 0
$global:size = 0

function CheckDatabase($cdb)
{
$connString = $cdb.LegacyDatabaseConnectionString
$conn = new-object system.data.sqlclient.sqlconnection
$conn.connectionstring = $connstring
$conn.open()

$sql = "select count(*) as count, sum(datalength(content)) as size from alldocstreams where id not in (select id from alldocs)";
$cmd = new-object system.data.sqlclient.sqlcommand($sql, $conn)
$reader = $cmd.executereader()

while($reader.read())
{
$count = [double]::parse($reader["count"].tostring())
$size = [double]::parse($reader["size"].tostring())

}

$reader.close()
$conn.close()

$global:Count += $count
$global:size += $size

}

$cdbs = get-spcontentdatabase

foreach ($cdb in $cdbs)
{
CheckDatabase $cdb
}

"Orphaned rows: " + $global:count
"Orphaned rows size: " + $global:size + " bytes"

Microsoft…you need to get us a cumulative updatehot fix RIGHT NOW.

UPDATE:  AUGUST 2012 Cumulative Update fixes this bug:
http://technet.microsoft.com/en-us/sharepoint/ff800847.aspx

Not one to say enjoy too, but at least I know what is going on now and so do you!
Chris

Follow me on twitter: @givenscj

Check out the previous blog post:
https://blogs.architectingconnectedsystems.com/blogs/cjg/archive/2012/04/20/Project-Server-PSI-WCF-bug-_2D00_-WSEC_5F00_CAT_5F00_UID-and-WSEC_5F00_GRP_5F00_UID.aspx