Migrating SqlServer(2008) named instances to schemas

I currently have a sofware with 80 customers. All share the same database structure, when data are differents.

We got 5 database for each customer.

Today, we have named instances, so the program is accessing to Bdd with something like :

• Connect to 127.0.0.1/CUSTOMER1
• select *开发者_如何学编程 from base1..table1

Now, we need to change, we got many instances.

The idea is to have unique SQLSERVER instance, obviously i want to avoid changing the code (VB.NET).

Just tried to play with schemas and users, seems to be an option, but since the schema is under the database, in can't make it works :

• Connect to 127.0.0.1/GLOBAL_INSTANCE as specific user (customer1)
• select * from base1..table1 -> error @ this point

When THIS works :

• Connect to 127.0.0.1/GLOBAL_INSTANCE as specific user (customer1)
• Use Base1
• select * from base1..table1 / or select * from table1

I can easily script some user or schema creation, and change connection strings for all customers, but i can't really change All requests: huge number.

Thanks for any response or idea.

Here's a high-level view of using schemas. The scripts creates a test database, creates two users in it (T1 and T2) and two schemas (S1 and S2). It then "logs in" as each user and creates tables as that user and therefor bound to that schema and then inserts some data and reads it out. Then it "logs out" (REVERT) and selects from both tables using schema-qualified names. If you comment out the drop parts you can log in as one of the users and perform an unqualified SELECT * FROM Test and it will find the correct schema automatically.

Run the code below with the sa or another administrator account (one that has permissions to create tables, logins, schemas and permision to GRANT)

--//Make sure we're using the correct credentials, this undoes any calls to EXECUTE AS ...
REVERT
GO

--//Switch to the master database
USE MASTER
GO

--//Create our test database
CREATE DATABASE Tester
GO

--//Swtich to the test database
USE Tester
GO

--//CREATE two logins, T1 and T2
CREATE LOGIN T1 WITH PASSWORD = 'T1'
CREATE USER T1 WITH DEFAULT_SCHEMA = S1

CREATE LOGIN T2 WITH PASSWORD = 'T2'
CREATE USER T2 WITH DEFAULT_SCHEMA = S2
GO

--//Give them permission to create tables
GRANT CREATE TABLE to T1
GRANT CREATE TABLE to T2
GO

--//Create two schemas, S1 and S2
CREATE SCHEMA S1 AUTHORIZATION T1
GO
CREATE SCHEMA S2 AUTHORIZATION T2
GO

--//Switch context to the T1 user
EXECUTE AS USER = 'T1'
GO

--//Create our table
CREATE TABLE Test
(
Col1 varChar(255)
)
GO

--//Insert some data
INSERT INTO Test VALUES ('This is from schema 1')
GO

--//Displays schema 1
SELECT * FROM Test

--//Switch back to the currently logged in user
REVERT
GO

--//Switch context to the T2 user
EXECUTE AS USER = 'T2'
GO

--//Create our table
CREATE TABLE Test
(
Col1 varChar(255)
)
GO

--//Insert some data
INSERT INTO Test VALUES ('This is from schema 2')
GO

--//Displays schema 2
SELECT * FROM Test
GO

--//Switch back to the currently logged in user
REVERT
GO

--//As the main user now select from both tables by schema-prefix
SELECT * FROM S1.Test
UNION
SELECT * FROM S2.Test


--//Cleanup everything that we just made
DROP TABLE S1.Test
DROP TABLE S2.Test

DROP SCHEMA S1
DROP SCHEMA S2

DROP LOGIN T1
DROP LOGIN T2
GO

USE MASTER
GO

DROP DATABASE Tester
GO

You don't need to GRANT table creation permission to the logins if you don't want to, you can instead just use a higher level account an do CREATE TABLE S1.Test(...). I just find it easier to do it this way so that I don't have to qualify everything all the time. After I create things then I can just REVOKE the permission.