I was restoring a database backup on my new project when I noticed that some of the stored procedures where owned by a specific user instead of ‘dbo.’ That’s a nono for several reasons — not all the procs were owned by the same user, plus it can get messy if you have two copies of the same proc, one owned by dbo & one owned by the user, plus if stored procs are owned by a specific user then you can’t remove the user from that database, plus other users won’t be able to access those procs by default. Plus other stuff. 🙂

Steve Shofield has a quick blog post about changing ownership using the INFORMATION_SCHEMA views, but I didn’t see an equivalent script for stored procedures that works in SQL Server 2000. So here’s my versions:

— convert tables to dbo
select ‘sp_changeobjectowner ”[‘ + table_schema + ‘].[‘ + table_name + ‘]”, ”dbo”’+char(13)+char(10)+‘go’ from information_schema.tables where table_schema <> ‘dbo’
— convert stored procedures to dbo
select ‘sp_changeobjectowner ”[‘ + routine_schema + ‘].[‘ + routine_name + ‘]”, ”dbo”’+char(13)+char(10)+‘go’ from information_schema.routines where routine_schema <> ‘dbo’

Log into query analyzer, hit CTRL+T to get results in text mode, run the above, and you’ll see the in results window some SQL code that you can copy & paste into the query analyzer & run again. You may get a warning saying “Caution: Changing any part of an object name could break scripts and stored procedures”, which basically means that if your stored procedures or queries are written to refer to objects by the username.objectname syntax, they might break. But that probably isn’t an issue for you if you’re running this script.