change all stored procedure ownership in ms sql database

When you migrated MS SQL database to new server there is change that you may see error like “Stored Procedure not found”. Mainly this comes due to a change in dbo. Please make sure that the stored procedure is owned by the correct database user. If the user is different  you can correct it by following steps.

Step1: Take the query analyzer and execute below queries

use DATABSE_NAME
SELECT ‘ALTER SCHEMA dbo TRANSFER [‘ + SysSchemas.Name + ‘].[‘ + DbObjects.Name + ‘];’
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = ‘DATABSE_NAME
AND (DbObjects.Type IN (‘U’, ‘P’, ‘V’))

You will see a bunch of results in the output window

like : ALTER SCHEMA dbo TRANSFER [DATABSE_NAMEl].[STORED-PROCEDURE-NAME];

Step2: Copy all results and paste it on to a new query window or same window. Please execute the queries and this will do the fixes.

facebooktwittergoogle_plusredditpinterestlinkedinmail

LEAVE A COMMENT