Hi There,

I am hoping someone can help me with the following situation. I have been reading articles and googling but i cannot seem to find the answers i need.

I have two databases on SQL Azure. Both 100MB of size and both running on Standard S2. I need greater performance on both databases but i don't have budget to pay more. Please note that the usage is low, it is just i want my queries to perform faster (i have done all the fine tuning of my databases, queries and indexes that i can do). So my plan is to merge both databases to one database and move this database to a higher tier and remove the old databases (result is same price but better performance tier). 

So at the moment i have Database 1 with dbo schema and dbo user, and Database 2 with dbo schema and dbo user.

In both databases everything is running under dbo so i have not configured anything with permissions or schemas or users (and my skills in this area are lacking a little bit).

Now I want to create Database 3 that has Schema A and User A and Schema B and User B. User A should be able to do everything in schema A and user B should be able to do everything in Schema B. The users should only be able to see their own schemas and not the other.

Could someone please help me with how to create my new database, schemas and users. I just want to get the new database set up. I can handle the migration of the old databases after that. I can create the new database through my portal, but then once i connect via SSMS to sql azure, i do not know the commands to create the schemas, users and ensure the correct permissions.

Any help would be appreciated.

Thanks

In SSMS you can right click the database and select "generate scripts". Here you should select "Script entire database" and for the output select "Save to new query window".

In the generated script replace "Database 1" with "Database 3" and replace "[dbo]" with "Schema A".  If there's a "[dbo].[sp_fulltext_database]" statement, keep that as [dbo].

Run this script.

Do the same as described above for Database 2 but here remove all the Create and Alter Database statements.

If you run this script, you should have a new database with 2 schema's with all the tables from your old databases.

Hi,

Thank you for the response, I have just done as you said and analysed the generated script. I feel this does not complete what i am looking for. Moving the tables and stored procedures etc. to the new database is not a problem for me, it is the sql around creating the schemas, roles and permissions is where i am getting stuck, and your solution doesn't cover that i think.

If you could help me do the following on an empty azure sql databsae it would be good

1. SQL to create two new schemas

2. SQL to create two new users

3. SQL to make each user owner of its own schema

4. SQL to assign necessary permissions so that each user has full access to only its own schema and not the other

Thank You

David

Thanks to the following article, i have made some progress

http://geekswithblogs.net/hroggero/archive/2011/10/05/solving-schema-separation-challenges.aspx

Once i create my new empty database, first I need to create my schemas

CREATE SCHEMA SchemaA GO CREATE SCHEMA SchemaB GO

Then I need to create my logins against the master database

CREATE LOGIN UserA WITH PASSWORD = 'p@ssw0rd001' CREATE LOGIN UserB WITH PASSWORD = 'p@ssw0rd002'

Then i create my users against my new empty database

CREATE  USER UserA   FORLOGINUserA  CREATE  USER UserA   FORLOGIN UserA 

Then i grant permission to my new users to my new schemas

GRANT  SELECT, EXECUTE ON SCHEMA::SchemaA TO UserA

GRANT SELECT ,EXECUTE ON SCHEMA::SchemaB TO UserB

 So, this gives me one database with 2 schemas, and two users which can only see their own schemas. My only additional question, is that i would like to give my UserA full access to schema A. My code above only ives  select and execute. How can i give access to create all database objects within the schema and to read, write to all tables within the schema.

Thank You

David

You are nearly there.

Indeed first create the logins on Master and the create the users on the actual database.

For you use case you probably need 

GRANT TAKE OWNERSHIP ON SCHEMA::SchemaA TO UserA

AND

GRANT TAKE OWNERSHIP ON SCHEMA::SchemaB TO UserB

That way the users are owners of the schema and should have all the necessary permissions.

I have done as you said but my user still does not have full access to the schema. I need the user to be able to create table, views, stored procedures. Basically i want the user to be like a database owner but only within its own schema. Is this possible?

You should probably also add the necessary Create permissions separately.

So GRANT CREATE TABLE TO UserA, GRANT CREATE PROCEDURE TO UserA and GRANT CREATE VIEW TO UserA

Two points for you: 

1) If you grow to more users, you may find Elastic Database Pools to be cost-effective and provide greater isolation guarantees. This feature allows you to share DTU resources between databases within the pool.

2) For your immediate scenario, the following T-SQL should achieve what you are looking for:

CREATE SCHEMA SchemaA
go
CREATE SCHEMA SchemaB
go

-- note: using contained users rather than logins for better performance
-- see https://msdn.microsoft.com/library/ff929188.aspx for more information
CREATE USER UserA WITH PASSWORD = '{{SomeStrongPassword}}', DEFAULT_SCHEMA = [SchemaA]
CREATE USER UserB WITH PASSWORD = '{{SomeStrongPassword}}', DEFAULT_SCHEMA = [SchemaB]
go

-- change ownership to avoid elevation via ownership-chaining
ALTER AUTHORIZATION ON SCHEMA::SchemaA TO UserA
ALTER AUTHORIZATION ON SCHEMA::SchemaB TO UserB
go

-- grant the necessary permissions to both users
GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO UserA;
GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO UserB;
go

Blue Rasher,

Thank you for your help, it has been very useful for me.

David

Tommy,

Thank you for the sql. I believe that will do the trick. Thank you so much.

Regarding the elastic pools, I have just read the article you linked. I was not aware of this. It is very interesting. I need to spend some time to understand that fully.

I am actually now considering moving both databases to P1. Both databases that i currently have are average about 40% DTU. So you would think S3 would be more than enough, but actually we found that moving from S1 to S2 gave a huge increase in performance of our application, but we still do not have enough. So the issue is not that we are going to use all DTUs, but rather we need greater processing power in the database. It is a heavily data driven application we use and SQL server is doing a lot of the work for us. On this information, does it sound like elastic pools would be a good option for both databases running on P1. If i should start a new thread for this please advise.

Thank You

David

Two points for you: 

1) If you grow to more users, you may find Elastic Database Pools to be cost-effective and provide greater isolation guarantees. This feature allows you to share DTU resources between databases within the pool.

2) For your immediate scenario, the following T-SQL should achieve what you are looking for:

CREATE SCHEMA SchemaA
go
CREATE SCHEMA SchemaB
go

-- note: using contained users rather than logins for better performance
-- see https://msdn.microsoft.com/library/ff929188.aspx for more information
CREATE USER UserA WITH PASSWORD = '{{SomeStrongPassword}}', DEFAULT_SCHEMA = [SchemaA]
CREATE USER UserB WITH PASSWORD = '{{SomeStrongPassword}}', DEFAULT_SCHEMA = [SchemaB]
go

-- change ownership to avoid elevation via ownership-chaining
ALTER AUTHORIZATION ON SCHEMA::SchemaA TO UserA
ALTER AUTHORIZATION ON SCHEMA::SchemaB TO UserB
go

-- grant the necessary permissions to both users
GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO UserA;
GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO UserB;
go

• Marked as answer by David Brosnan 1 hour 22 minutes ago

Two points for you: 

1) If you grow to more users, you may find Elastic Database Pools to be cost-effective and provide greater isolation guarantees. This feature allows you to share DTU resources between databases within the pool.

2) For your immediate scenario, the following T-SQL should achieve what you are looking for:

CREATE SCHEMA SchemaA
go
CREATE SCHEMA SchemaB
go

-- note: using contained users rather than logins for better performance
-- see https://msdn.microsoft.com/library/ff929188.aspx for more information
CREATE USER UserA WITH PASSWORD = '{{SomeStrongPassword}}', DEFAULT_SCHEMA = [SchemaA]
CREATE USER UserB WITH PASSWORD = '{{SomeStrongPassword}}', DEFAULT_SCHEMA = [SchemaB]
go

-- change ownership to avoid elevation via ownership-chaining
ALTER AUTHORIZATION ON SCHEMA::SchemaA TO UserA
ALTER AUTHORIZATION ON SCHEMA::SchemaB TO UserB
go

-- grant the necessary permissions to both users
GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO UserA;
GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO UserB;
go

• Marked as answer by David Brosnan Wednesday, September 02, 2015 5:48 AM