/* 1- create database d1;*/
create database d1;

/* 5- create new login login1. */
CREATE LOGIN [Login1] WITH PASSWORD=N'Login&', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

/* 2- create new user u1 */
USE [d1]
CREATE USER [u1] FOR LOGIN [Login1] ;
EXEC sp_addrolemember N'db_owner', N'u1'
GO


/* 3- create a schema s1 for u1. */
create schema s1;
go

/* 4- set s1 as default schema for u1. */
ALTER USER [u1] WITH DEFAULT_SCHEMA=[s1] ;
go

/* 6- map the login to database d1 using user u1 with default schema s1. */
-- already mapped by step 2

/* 7 */
-- Switch to user ( I need to be sysadmin !!)
Execute as login='Login1'
Create table Mytable (col1 int not null);

-- switch back to myself
revert;

Select *
from information_schema.tables 
where table_name = 'Mytable'

/*
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE
d1				s1				Mytable		BASE TABLE
*/

Print 'No problem'

/* Clean up
use master

drop database d1 ;
drop login Login1;
*/

     Jul 13  

Sometimes this is me