T-SQL スキーマ(Schema)無しでの呼出しでどのスキーマのものが使われるのか

test.sql

use [master]
create database [SchemaTest] containment=partial
go
use [SchemaTest]
go

-- スキーマ
create schema [S1]
go
create schema [S2]
go
create schema [S3]
go

-- ユーザ
create user [U1] without login with default_schema = [S1]
go
create user [U2] without login  with default_schema = [S2]
go

grant select, execute on schema::[S1] to [U1]
go
grant select, execute on schema::[S2] to [U2]
go
grant select/*, execute*/ on schema::[dbo] to [U1]
go
grant select/*, execute*/ on schema::[dbo] to [U2]
go

-- テーブル
create table [dbo].[dboだけTable] ( [Name] nvarchar(20), [Comment] nvarchar(20) )
insert into [dbo].[dboだけTable] values (N'dboだけTable', N'dboだよー')
go
create table [dbo].[dboもあるTable] ( [Name] nvarchar(20),  [Comment] nvarchar(20) )
insert into [dbo].[dboもあるTable] values (N'dboもあるTable', N'dboだよー')
go
create table [S1].[dboもあるTable] ( [Name] nvarchar(20), [Comment] nvarchar(20) )
insert into [S1].[dboもあるTable] values (N'dboもあるTable', N'S1だよー')
go
create table [S2].[dboもあるTable] ( [Name] nvarchar(20), [Comment] nvarchar(20) )
insert into [S2].[dboもあるTable] values (N'dboもあるTable', N'S2だよー')
go
create table [S1].[dboないTable]  ( [Name] nvarchar(20), [Comment] nvarchar(20) )
insert into [S1].[dboないTable] values (N'dboないTable', N'S1だよー')
go
create table [S2].[dboないTable]  ( [Name] nvarchar(20), [Comment] nvarchar(20) )
insert into [S2].[dboないTable] values (N'dboないTable', N'S2だよー')
go
create table [S3].[S3しかないTable]  ( [Name] nvarchar(20), [Comment] nvarchar(20) )
insert into [S3].[S3しかないTable] values (N'S3しかないTable', N'S3だよー')
go

-- ファンクション
create function [dbo].[Getdboだけ]()
returns nvarchar(20)
as 
begin
declare @r nvarchar(20) = (select top(1) [Comment] from [dboだけTable])
return @r
end
go
create function [S1].[Getdboだけ]()
returns nvarchar(20)
as 
begin
declare @r nvarchar(20) = (select top(1) [Comment] from [dboだけTable])
return @r
end
go
create function [S2].[Getdboだけ]()
returns nvarchar(20)
as 
begin
declare @r nvarchar(20) = (select top(1) [Comment] from [dboだけTable])
return @r
end
go
create function [S3].[Getdboだけ]()
returns nvarchar(20)
as 
begin
declare @r nvarchar(20) = (select top(1) [Comment] from [dboだけTable])
return @r
end
go
create function [dbo].[Getdboもある]()
returns nvarchar(20)
as 
begin
declare @r nvarchar(20) = (select top(1) [Comment] from [dboもあるTable])
return @r
end
go
create function [S1].[Getdboもある]()
returns nvarchar(20)
as 
begin
declare @r nvarchar(20) = (select top(1) [Comment] from [dboもあるTable])
return @r
end
go
create function [S2].[Getdboもある]()
returns nvarchar(20)
as 
begin
declare @r nvarchar(20) = (select top(1) [Comment] from [dboもあるTable])
return @r
end
go
create function [S3].[Getdboもある]()
returns nvarchar(20)
as 
begin
declare @r nvarchar(20) = (select top(1) [Comment] from [dboもあるTable])
return @r
end
go
create function [dbo].[GetS3しかないTable]()
returns nvarchar(20)
as 
begin
declare @r nvarchar(20) = (select top(1) [Comment] from [S3しかないTable])
return @r
end
go
create function [S1].[GetS3しかないTable]()
returns nvarchar(20)
as 
begin
declare @r nvarchar(20) = (select top(1) [Comment] from [S3しかないTable])
return @r
end
go
create function [S2].[GetS3しかないTable]()
returns nvarchar(20)
as 
begin
declare @r nvarchar(20) = (select top(1) [Comment] from [S3しかないTable])
return @r
end
go
create function [S3].[GetS3しかないTable]()
returns nvarchar(20)
as 
begin
declare @r nvarchar(20) = (select top(1) [Comment] from [S3しかないTable])
return @r
end
go
-- ストアド
create procedure [dbo].[Execdboだけ]
as
select * from [dboだけTable]
go
create procedure [S1].[Execdboだけ]
as
select * from [dboだけTable]
go
create procedure [S2].[Execdboだけ]
as
select * from [dboだけTable]
go
create procedure [S3].[Execdboだけ]
as
select * from [dboだけTable]
go
create procedure [dbo].[Execdboもある]
as
select * from [dboもあるTable]
go
create procedure [S1].[Execdboもある]
as
select * from [dboもあるTable]
go
create procedure [S2].[Execdboもある]
as
select * from [dboもあるTable]
go
create procedure [S3].[Execdboもある]
as
select * from [dboもあるTable]
go
create procedure [dbo].[ExecS3しかないTable]
as
select * from [S3しかないTable]
go
create procedure [S1].[ExecS3しかないTable]
as
select * from [S3しかないTable]
go
create procedure [S2].[ExecS3しかないTable]
as
select * from [S3しかないTable]
go
create procedure [S3].[ExecS3しかないTable]
as
select * from [S3しかないTable]
go
create procedure [dbo].[ExecdboもあるTableだけどストアドはスキーマはdboだけ]
as
select * from [dboもあるTable]
go
-- シノニム
-- (S2 だけは、S3テーブルへ自分のスキーマでシノニム貼る)
create synonym [S2].[S3しかないTable] for [S3].[S3しかないTable]
-- dbo の function、procedure に S1、S2 が自分のスキーマで別名を付ける
create synonym [S1].[シノニム経由でGetdboもある] for [dbo].[Getdboもある]
create synonym [S1].[シノニム経由でExecdboもある] for [dbo].[Execdboもある]
create synonym [S1].[ExecdboもあるTableだけどストアドはスキーマはdboだけ] for [dbo].[ExecdboもあるTableだけどストアドはスキーマはdboだけ]
create synonym [S2].[シノニム経由でGetdboもある] for [dbo].[Getdboもある]
create synonym [S2].[シノニム経由でExecdboもある] for [dbo].[Execdboもある]
create synonym [S2].[ExecdboもあるTableだけどストアドはスキーマはdboだけ] for [dbo].[ExecdboもあるTableだけどストアドはスキーマはdboだけ]

-- スキーマ指定なし普通の select
select * from [dboもあるTable]  -- dbo
go
execute as user = 'U1'
select * from [dboもあるTable]  -- S1(default_schema)
revert
go
execute as user = 'U2'
select * from [dboもあるTable]  -- S2(default_schema)
revert
go

-- エラー
--select * from [dboないTable]
go
execute as user = 'U1'
select * from [dboないTable]  -- S1(default_schema)
revert
go
execute as user = 'U2'
select * from [dboないTable]  -- S2(default_schema)
revert
go

select * from [dboだけTable]  -- dbo
go
execute as user = 'U1'
--select * from [dboだけTable]  -- dbo(dbo の select 権限ないとエラー)
revert
go
execute as user = 'U2'
--select * from [dboだけTable]  -- dbo(dbo の select 権限ないとエラー)
revert
go

-- ファンクション
-- dboだけテーブル
select [dbo].[Getdboだけ]()
select [S1].[Getdboだけ]()
select [S2].[Getdboだけ]()
select [S3].[Getdboだけ]()
go
execute as user = 'U1'
select [S1].[Getdboだけ]() -- dbo(dbo の select 権限なくてもセーフ)
revert
go
execute as user = 'U2'
select [S2].[Getdboだけ]() -- dbo(dbo の select 権限なくてもセーフ)
revert
go
-- dboもあるテーブル
select [dbo].[Getdboもある]() -- dbo
select [S1].[Getdboもある]() -- S1
select [S2].[Getdboもある]() -- S2
select [S3].[Getdboもある]() -- dbo (S3 は table 無いので、dbo が使われた)
go
execute as user = 'U1'
select [S1].[Getdboもある]() -- S1
select [S1].[シノニム経由でGetdboもある]() -- dbo (シノニムで S1.～ と呼んでるけど、呼ばれてる function は dbo の物なので。ただ dbo の execute が無くてもエラーにならない)
revert
go
execute as user = 'U2'
select [S2].[Getdboもある]() -- S2
select [S2].[シノニム経由でGetdboもある]() -- dbo (シノニムで S2.～ と呼んでるけど、呼ばれてる function は dbo の物なので。ただ dbo の execute が無くてもエラーにならない)
revert
go
-- S3しかないテーブル
--select [dbo].[GetS3しかないTable]() -- S3 にしかテーブルが無いので、テーブルが無いエラー
select [S3].[GetS3しかないTable]() -- S3 (デフォルトスキーマではなくても、ファンクションのスキーマに引きずられる)
go
execute as user = 'U1'
--select [S1].[GetS3しかないTable]() -- S3 にしかテーブルが無いので、テーブルが無いエラー
revert
go
execute as user = 'U2'
select [S2].[GetS3しかないTable]() -- S3 シノニムで S2.S3しかないTable を検索したら、 S3.S3しかないTable が見つかるので
revert
go

-- ストアド
-- dboだけ を スキーマ指定ありと無しで
exec [Execdboだけ] -- dbo
exec [dbo].[Execdboだけ] -- dbo
exec [S1].[Execdboだけ] -- dbo
exec [S2].[Execdboだけ] -- dbo
exec [S3].[Execdboだけ] -- dbo
go
execute as user = 'U1'
exec [Execdboだけ] -- dbo (S1.Execdboだけ が呼ばれている)
--exec [dbo].[Execdboだけ] -- エラー (dbo の exec 権限がない)
exec [S1].[Execdboだけ] -- dbo
revert
go
execute as user = 'U2'
exec [Execdboだけ] -- dbo (S2.Execdboだけ が呼ばれている)
--exec [dbo].[Execdboだけ] -- エラー (dbo の exec 権限がない)
exec [S2].[Execdboだけ] -- dbo
revert
go
-- dboもある を スキーマ指定ありと無しで
exec [Execdboもある] -- dbo
exec [dbo].[Execdboもある] -- dbo
exec [S1].[Execdboもある] -- S1
exec [S2].[Execdboもある] -- S2
exec [S3].[Execdboもある] -- dbo
go
execute as user = 'U1'
exec [Execdboもある] -- S1 (S1.Execdboもある が呼ばれている)
-- exec [dbo].[Execdboもある] -- エラー (dbo の exec 権限がない)
exec [S1].[Execdboもある] -- S1
exec [S1].[シノニム経由でExecdboもある] -- dbo (シノニムで S1.～ と呼んでるけど、呼ばれてる procedure は dbo の物なので。ただ dbo の execute が無くてもエラーにならない)
revert
go
execute as user = 'U2'
exec [Execdboもある] -- S2 (S2.Execdboもある が呼ばれている)
-- exec [dbo].[Execdboもある] -- エラー (dbo の exec 権限がない)
exec [S2].[Execdboもある] -- S2
exec [S2].[シノニム経由でExecdboもある] -- dbo (シノニムで S2.～ と呼んでるけど、呼ばれてる procedure は dbo の物なので。ただ dbo の execute が無くてもエラーにならない)
revert
go
-- S3しかないテーブル
-- exec [ExecS3しかないTable] -- S3 にしかテーブルが無いので、テーブルが無いエラー
exec [S3].[ExecS3しかないTable] -- S3 (デフォルトスキーマではなくても、ストアドのスキーマに引きずられる)
go
execute as user = 'U1'
-- exec [ExecS3しかないTable] -- S3 にしかテーブルが無いので、テーブルが無いエラー
revert
go
execute as user = 'U2'
exec [ExecS3しかないTable] -- S3 シノニムで S2.S3しかないTable を検索したら、 S3.S3しかないTable が見つかるので
revert
go
-- dboしかストアドが無いがシノニムで同名のものを用意している時にスキーマ無しで呼び出したら
exec [ExecdboもあるTableだけどストアドはスキーマはdboだけ] -- dbo
go
execute as user = 'U1'
exec [ExecdboもあるTableだけどストアドはスキーマはdboだけ] -- dbo (シノニムで S1.～ が呼ばれるけど、呼ばれてる procedure は dbo の物なので。ただ dbo の execute が無くてもエラーにならない)
revert
go
execute as user = 'U2'
exec [ExecdboもあるTableだけどストアドはスキーマはdboだけ] -- dbo (シノニムで S2.～ が呼ばれるけど、呼ばれてる procedure は dbo の物なので。ただ dbo の execute が無くてもエラーにならない)
revert
go

use [master]
go
drop database [SchemaTest]
go