January 11, 2010 at 3:53 am
I did the following:
1- create database d1.
2- create new user u1
3- create a schema s1 for u1.
4- set s1 as default schema for u1.
5- create new login login1.
6- map the login to database d1 using user u1 with default schema s1.
i write a statement to create a new table (without specifying the schema as a prefix), the table was created in the dbo schema instead of the s1 (the default schema).
i modified the statement to include the s1.tablename, and the table was created successfully in the s1 schema.
When writing a select statement to get data from the the table without specifying the schema, the data has been retrieved from the s1 table (the default schema table), which is the expected result.
So why the table was created in dbo schema instead of the default schema?
January 11, 2010 at 4:31 am
/* 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_CATALOGTABLE_SCHEMATABLE_NAMETABLE_TYPE
d1s1MytableBASE TABLE
*/
Print 'No problem'
/* Clean up
use master
drop database d1 ;
drop login Login1;
*/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply