I was doing some work item type definition changes on Azure DevOps Server 2022 as part of a cloud migration I'm working on for a customer. A bunch of work item modification scripts that had been working were suddenly not working. The witadmin importwitd commands were failing with vague errors saying "permission denied". When I looked into the /tfs/_oi/ website on the server there were strange exception messages saying Cannot find the object "dbo.WorkitemTypes" because it does not exist or you do not have permissions. Turns out there were permissions problems in the SQL Server that's used by Azure DevOps.

Here's what was going on and what I did to fix it.

WitAdmin ImportWITD command line errors

If you've got an on-premises Azure DevOps Server with a Team Project Collection that uses the XML Process Model, you can modify the work item type definitions for your project using a utility called WitAdmin. The basic gist is that you use witadmin exportwitd to export the XML for your work item type definition (WITD), modify it with your changes, and then use witadmin importwitd to write the changes back to Azure DevOps.

By the way, when you're working on an on-prem Azure DevOps Server to cloud Azure DevOps Services migration, there's a good chance that you'll need to make hundreds -- if not thousands -- of work item type definition edits in order to get you team project collections to successfully migrate to the cloud.

So anyway, I'd run one of my witadmin importwitd command scripts and then I'd get a message that said something like "access denied" or "permission denied". That was strange because I was a Team Project Collection Administrator on this machine and I definitely had permissions.

Drilling in using the Azure DevOps Server Monitoring Page ("_oi")

In Azure DevOps Server, there's semi-hidden feature that lets you see how your server is doing, to check the status of jobs, and view detailed error messages. To access that page, you'll go to the following URL: https://<yourserver>/tfs/_oi

When I went to this page and went to the Activity Log, I could see that the exact error messages that were hiding behind the witadmin failures.

In short, witadmin.exe was getting SQL exceptions on a stored procedure that's part of Azure DevOps. The stored procedure prc_CreateDefaultWorkItemType was getting called and then it was throwing off the following error:

Cannot find the object "dbo.WorkitemTypes" because it does not exist or you do not have permissions.

Getting this error at all is utterly crazy because I'm an admin with sysadmin rights in the database. And to layer on the craziness, the dbo.WorkItemTypes table definitely exists or a whole lot of other things would be broken.

Permissions Problem in prc_CreateDefaultWorkItemType 'EXECUTE AS VssfAdmin'

My theory on what was going wrong was that someone on the customer's DBA team had made a modification to the permissions in the Azure DevOps Server database.

1. Ultimately this proved out to be true and...
2. THIS IS A RADIOACTIVELY BAD IDEA!!! DON'T MODIFY THE DATABASE PERMISSIONS YOURSELF BECAUSE YOU'LL DEFINITELY BREAK STUFF!!!

I started by looking at the source code for prc_CreateDefaultWorkItemType on my Azure DevOps Server 2022 instance -- aka. a server that I'd definitely never done this "customization".

The start of the definition for the stored procedure has a little bit of code that says WITH EXECUTE AS 'VssfAdmin'. In SQL Server, stored procedures by default execute using the context and permissions of the caller to that procedure. But T-SQL has an option called EXECUTE AS that tells SQL Server to run the stored procedure using the permissions of another user -- in this case, it runs in the context of a user called VssfAdmin.

Now this is why editing the Azure DevOps SQL Server permissions is such a bad idea. Did you know that Azure DevOps used a user called VssfAdmin to execute stored procedures? Because I sure didn't. This use is defined internally to your Team Project Collection (TPC) database.

If you opened your TPC database using SQL Server Management Studio (SSMS) and navigated to Security | Users, you'll see that VssfAdmin user.

Drilling in a little further to the permissions for this user, in the Membership section for this user, the default settings are for the user to be a member of the db_owner role for the database. Basically, this user has A LOT of permissions. But you shouldn't go messing with these permissions because 1) you don't know what you're doing, 2) it'll tend to break stuff, and 3) Azure DevOps Server 2022 manages these permissions automatically via the Azure DevOps Admin Console.

Missing Permissions for VssfAdmin User

On my customer's Azure DevOps SQL Server, the db_owner role had been removed for the VssfAdmin user. That was why those permissions problems were popping up whenever I'd run a witadmin importwitd command. Now that I think of it, when I was first trying to diagnose the permissions problems using witadmin, I noticed that I could run a witadmin exportwitd just fine. That was a clue that pointed to database READ permissions being ok but WRITE permissions were broken.

Anyway. The fix was just a matter of checking that db_owner membership checkbox for the VssfAdmin user. But finding this problem took hours and hours to figure out.

Another Error Message That's Probably Related

This same server also had problems applying some patches for Azure DevOps Server 2022. The error I was getting for that is as follows:

System.Data.SqlClient.SqlException (0x80131904): VIEW SERVER PERFORMANCE STATE permission was denied on object 'server', database 'master'.

The user does not have permission to perform this action.

   at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.MapException(SqlException ex, QueryExecutionState queryState)

   at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.HandleException(Exception exception)

   at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.ExecuteCommand(ExecuteType executeType, CommandBehavior behavior, String performanceGroupName)

   at Microsoft.VisualStudio.Services.CircuitBreaker.Command.<>c__DisplayClass7_0.<Execute>b__0()

   at Microsoft.VisualStudio.Services.CircuitBreaker.CommandAsync.<Execute>d__30.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at Microsoft.VisualStudio.Services.CircuitBreaker.CommandAsync.<Execute>d__30.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at Microsoft.VisualStudio.Services.CircuitBreaker.Command.Execute(Action run, Action fallback)

   at Microsoft.TeamFoundation.Framework.Server.DatabaseCircuitBreaker.ExecuteCommandWithBaseCircuitBreaker(ExecuteType executeType, CommandBehavior behavior, String performanceGroupName)

   at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.ExecuteReader()

   at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlSecurityComponent.GetAvailabilityReplicas(String domainExtension, String port)

   at Microsoft.TeamFoundation.Framework.Server.TeamFoundationDatabaseManagementService.GetReplicaNodesFromAvailabilityGroup(ISqlConnectionInfo dataTierConnectionInfo, ITFLogger logger)

   at Microsoft.TeamFoundation.Admin.VerifySqlAvailabilityGroup.CheckAvailabilityGroupHealthForSqlInstance(ISqlConnectionInfo connectionInfo)

   at Microsoft.TeamFoundation.Admin.VerifySqlAvailabilityGroup.Run(ActivityContext context)

   at Microsoft.TeamFoundation.Admin.Engine.AdminNode.RunNode()

   at Microsoft.TeamFoundation.Admin.Engine.AdminNode.Run(String& message, Boolean& continueOnError)

Summary

In short -- the moral of this story: don't ever modify the SQL Server permissions for Azure DevOps yourself.

I hope this helps.

-Ben