2

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.

So:

me.Employees
me.vw_EmployeesStuff
me.proc_SaveEmployee

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

1

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

0

Unchecking sysadmin seemed to fix this immediate issue, but caused a new one. Now the 'me' user cannot expand any server nodes in SSMS.

It looks like ultimately some of our logins were orphaned because we created the databases from backups from a different server.

Community wiki answer based on comments left by the question author

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.