--THIS DEMO SHOWCASES THE NEW DDL FOR USER AND SCHEMA 
--AND DEMONSTRATES THE CONCEPT OF DEFAULT SCHEMA
use master
select @@version
--create the database to be used for this demo
create database user_schema_db

--create logins using the new DDL
CREATE LOGIN Test1 WITH PASSWORD = 'A!234BCD'
CREATE LOGIN Test2 WITH PASSWORD = 'W@789XYZ'
CREATE LOGIN Test3 WITH PASSWORD = 'M#456NOP'
--switch to the database
use user_schema_db
--create a user in the database to corresponding to 
--one of the newly created logins notice that 
--the default schema is explicitly specified.
CREATE USER Test1 FOR LOGIN Test1 
WITH DEFAULT_SCHEMA = Test1Schema

--create the schema to be used as the default schema...
--...mark it to be owned by the user just created
CREATE SCHEMA Test1Schema AUTHORIZATION Test1
--create another user and make him point to the 
--same default schema, but not own it
CREATE USER Test2 FOR LOGIN Test2 
WITH DEFAULT_SCHEMA = Test1Schema
--create another user but do not assign him a 
--default schema
CREATE USER Test3 FOR LOGIN Test3
--Query the catalog views to see the 
--relevant information
select * from sys.database_principals 
where name like '%Test%'

select * from sys.schemas
--find out the owner names of schemas
select B.name as schema_name ,B.schema_id, 
A.name as owner_name, A.principal_id as owner_ID 
FROM sys.database_principals as A, sys.schemas as B
where A.principal_id = B.principal_id 
-- AND B.name = 'Test1Schema'

--Lets demonstrate name resolution

--create a table in the Test1Shcema
CREATE TABLE Test1Schema.Tab1 (c1 char (50))
Insert into Test1Schema.Tab1 
values ('This is the TEST1SCHEMA schema')

select * from Test1schema.Tab1
--create a table of the same name in the DBO schema
CREATE TABLE DBO.Tab1 (c1 char (50))
Insert into dbo.tab1 values ('This is the DBO schema')
select * from dbo.tab1

--setuser to Test2 who has Test1schema as the default 
--schema, but does not own it
setuser 'Test2'
select * from tab1
--notice the error message: the schema is Test1schema
--so lets grant the permissions and see what happens
setuser
grant select on test1schema.tab1 to Test2,Test3
grant select on dbo.tab1 to Test2,Test3
--now setuser to test2
--notice it hits the test1schema which is 
--the default schema
setuser 'test2'
select * from tab1
setuser
--setuser to test3 who does not have a default schema...
--it is therefore dbo
--notice it hits the DBO shcema.
setuser 'test3'
select * from tab1
setuser





