Change Schema Name on Tables and Stored procedures

If you get a Stored Procedure not found message, it could be a permissions or a schema ownership problem. If a permission problem, you need to Grant execute capability to the procedure like this:

GRANT EXEC ON procPartBrandByPartNumberBrandIDSelect TO PUBLIC
GO

To change the ‘schema’ name on an object from ‘username’ to ‘dbo’ in SQL Server 2005, you will need to use the ALTER SCHEMA command. The following script will generate the ALTER SCHEMA commands for all procedures that are not ‘dbo’.

Change Stored procedures in sql 2005

SELECT 'ALTER SCHEMA dbo TRANSFER [' + s.Name + '].' + p.Name  
FROM sys.Procedures p 
INNER JOIN sys.Schemas s on p.schema_id = s.schema_id 
WHERE s.Name <> 'dbo'

It would create this kind of output.

ALTER SCHEMA dbo TRANSFER [user1].spAuthors1  
ALTER SCHEMA dbo TRANSFER [user1].spAuthors2  
ALTER SCHEMA dbo TRANSFER [user1].spAuthors3

You would copy/paste in a new query window and execute. Then refresh SQL Management studio and the stored procedures would be dbo.spAuthors1, dbo.spAuthors2, dbo.spAuthors3. Note that you must escape the schema name with brackets in case there is a backslash in the name.

Change Tables in sql 2005

SELECT 'ALTER SCHEMA dbo TRANSFER [' + s.Name + '].' + p.Name   
FROM sys.Tables p 
INNER JOIN sys.Schemas s on p.schema_id = s.schema_id 
WHERE s.Name <> 'dbo'

Alternatively, here is an article that describes the syntax using the ‘sp_changeobjectowner’ method:

https://www.sqlservercentral.com/articles/understanding-object-ownership↗

The attached script will also correct the ownership for 2005 and later databases.

Change Default Schema

If this problem continues to occur, it is probably because you have the database installed under a particular user. Your db admin should know what that username is. To prevent this from happening in the future, in the database, run the following:

ALTER USER Username WITH DEFAULT_SCHEMA = dbo
GO

after replacing Username with the username for the owner of the database.

Portions of this article were sourced from https://www.iislogs.com/steveschofield/change-schema-name-on-tables-and-stored-procedures-in-sql-server-2005↗

FixSchema.sql (1 KB)