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.

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

Best Answer

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

Related Solutions

Sql-server – SQL Server Schema Security

Yes. Simply grant the user that you create that lets them log into the SQL Server SELECT rights only on the view and not the table.

You could actually do all this through a single view.

Create a function that looks up the CustomerId based on the username. Then call that function as part of the view.

create view YourView as 
select * 
from YourTable where CustomerID = dbo.YourFunction(suser_sname())

Sql-server – SQL Server 2000 to SQL Server 2008 linked server issue

I've had this problem before, and while I didn't find a fix (as it appears from the error message, SQL Server seems to 'lose' the brackets when it's processing the query), I did find a couple of workarounds:

Using OPENQUERY will allow you to use the linked server name as-is.
(My preferred solution): create a synonym for the linked server and use that in your query:

CREATE SYNONYM MyLinkedTable FOR [DBNAME.DATABASE.ORGANIZATION.COM].DBName.schema.TableName

Then use it in place of that huge mess:

SELECT * FROM MyLinkedTable

Best Answer

Related Solutions

Sql-server – SQL Server Schema Security

Sql-server – SQL Server 2000 to SQL Server 2008 linked server issue

Related Question