TFS 2010 upgrade fails with a custom stored procedure in TFS databases

  • Article

Recently, I worked on TFS 2008 to TFS 2010 upgrade case and it failed during the project collection servicing with below error:

Error message from Collection upgrade log:

[Error @20:15:39.558] [2012-04-23 19:58:24Z][Error] schema.tsq Line 38989 Error: Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.

Stamping DB...

[Info @20:15:39.558] [2012-04-23 19:58:24Z][Informational] Microsoft.TeamFoundation.Framework.Server.CollectionServicingException: schema.tsq Line 38989 Error: Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.

Stamping DB... ---> System.Data.SqlClient.SqlException: Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.

We collected SQL server profiler traces for the error and saw the failure occurs while performing “GRANT EXEC ON dbo.[sp_MSforeachtable] TO TFSEXECROLE as shown below . The stored procedure sp_MSforeachtable is a custom stored procedure which was created manually in TFS 2008.

Error in profiler Trace:

clip_image002

We dropped the procedure “sp_MSforeachtable” and “Rerun job” under the Team project collection from Team Foundation Administration console and the collection servicing was succeeded.

 

image

Content developed by: Chandra Sekhar Viswanadha

Content reviewed by: Lakhminder Singh