Sql-server – Can’t get users default schema to change – SQL Server 2008

sql serversql-server-2008

We are migrating from a SQL Server 2008 instance on a shared host to our own box. Our issue is we have a bunch of tables, procs and views that are prefaced with a schema (let's call the schema 'me') other than dbo.



We're having issues now that we have restored the backups from the shared host to our new box. When logged in as 'me', we need the queries to run like this:

SELECT * FROM vw_EmployeesStuff

And not have to query like:

SELECT * FROM me.vw_EmployeesStuff

We've tried to change the default schema for the user from dbo to me, but it doesn't seem to be 'taking'. Any thoughts or help is much appreciated. TIA

Best Answer

You have to migrate all the users from the original instance using the procedure sp_help_revlogin.