גרי רשף

Use tempdb;

Go

Create Login MyLogin With Password='MyLogin', 

Default_Database=tempdb,Check_Policy=Off;

Go

Create User MyUser For Login MyLogin With Default_Schema=MySchema;

Go

Exec SP_AddRoleMember 'DB_DDLAdmin', 'MyUser'

Go

Create Schema MySchema Authorization MyUser;

Go

If Object_ID('T','U') Is Not Null Drop Table T;

Go

Create Table T(I Int);

Go

Execute As User='MyUser';

Select * From T;

Revert;

-----------------------------------------------------

If Object_ID('P_T1','P') Is Not Null Drop Proc P_T1;

Go

Create Proc P_T1 As

Select * From T;

Go

Grant Exec On P_T1 To MyUser;

Go

-----------------------------------------------------

If Object_ID('P_T2','P') Is Not Null Drop Proc P_T2;

Go

Create Proc P_T2 As

Exec('Select * From T;')

Go

Grant Exec On P_T2 To MyUser;

Go

-----------------------------------------------------

If Object_ID('P_T3','P') Is Not Null Drop Proc P_T3;

Go

Create Proc P_T3 With Execute As Owner As

Exec('Select * From T;')

Go

Grant Exec On P_T3 To MyUser;

Go

-----------------------------------------------------

Exec dbo.P_T1;

Exec dbo.P_T2;

Exec dbo.P_T3;

Execute As User='MyUser';

Exec dbo.P_T1;

Exec dbo.P_T2;

Exec dbo.P_T3;

Revert;

---------------------------------------------------------------------------------------------------------------------------------

If Object_ID('P_Exec_dbo_1','P') Is Not Null Drop Proc P_Exec_dbo_1;

Go

Create Proc P_Exec_dbo_1 With Execute As 'dbo' As

Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];

Go

Grant Exec On P_Exec_dbo_1 To MyUser;

Go

---------------------------------------------------------------------------------------------------------------------------------

If Object_ID('MySchema.P_Exec_dbo_2','P') Is Not Null Drop Proc MySchema.P_Exec_dbo_2;

Go

Create Proc MySchema.P_Exec_dbo_2 With Execute As 'dbo' As

Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];

Go

---------------------------------------------------------------------------------------------------------------------------------

Exec dbo.P_Exec_dbo_1;

Execute As User='MyUser';

Exec dbo.P_Exec_dbo_1;

Revert;

Exec MySchema.P_Exec_dbo_2;

Execute As User='MyUser';

Exec MySchema.P_Exec_dbo_2;

Revert;

---------------------------------------------------------------------------------------------------------------------------------

If Object_ID('P_Exec_Owner_1','P') Is Not Null Drop Proc P_Exec_Owner_1;

Go

Create Proc P_Exec_Owner_1 With Execute As Owner As

Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];

Go

Grant Exec On P_Exec_Owner_1 To MyUser;

Go

---------------------------------------------------------------------------------------------------------------------------------

If Object_ID('MySchema.P_Exec_Owner_2','P') Is Not Null Drop Proc MySchema.P_Exec_Owner_2;

Go

Create Proc MySchema.P_Exec_Owner_2 With Execute As Owner As

Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];

Go

---------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------

If Object_ID('P_Exec_Self_dbo_Geri','P') Is Not Null Drop Proc 

P_Exec_Self_dbo_Geri;

Go

Create Proc P_Exec_Self_dbo_GeriWith Execute As Self As

Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];

Go

Grant Exec On P_Exec_Self_dbo_Geri To MyUser;

Go

---------------------------------------------------------------------------------------------------------------------------------

Execute As User='MyUser';

If Object_ID('P_Exec_Self_dbo_MyUser','P') Is Not Null Drop Proc P_Exec_Self_dbo_MyUser;

Go

Create Proc dbo.P_Exec_Self_dbo_MyUser With Execute As Self As

Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];

Go

Revert;

Grant Exec On dbo.P_Exec_Self_dbo_MyUser To MyUser;

Go

---------------------------------------------------------------------------------------------------------------------------------

If Object_ID('MySchema.P_Exec_Self_MySchema_Geri','P') Is Not Null Drop Proc MySchema.P_Exec_Self_MySchema_Geri;

Go

Create Proc MySchema.P_Exec_Self_MySchema_Geri With Execute As Self As

Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];

Go

Grant Exec On MySchema.P_Exec_Self_MySchema_Geri To MyUser;

Go

---------------------------------------------------------------------------------------------------------------------------------

Execute As User='MyUser';

If Object_ID('MySchema.P_Exec_Self_MySchema_MyUser','P') Is Not Null Drop Proc MySchema.P_Exec_Self_MySchema_MyUser;

Go

Create Proc MySchema.P_Exec_Self_MySchema_MyUser With Execute As Self As

Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];

Go

Revert;

---------------------------------------------------------------------------------------------------------------------------------

Exec dbo.P_Exec_Self_dbo_Geri;

Execute As User='MyUser';

Exec dbo.P_Exec_Self_dbo_Geri;

Revert;

-----------------------------------------------------

Exec dbo.P_Exec_Self_dbo_MyUser;

Execute As User='MyUser';

Exec dbo.P_Exec_Self_dbo_MyUser;

Revert;

-----------------------------------------------------

Exec MySchema.P_Exec_Self_MySchema_Geri;

Execute As User='MyUser';

Exec MySchema.P_Exec_Self_MySchema_Geri;

Revert;

-----------------------------------------------------

Exec MySchema.P_Exec_Self_MySchema_MyUser;

Execute As User='MyUser';

Exec MySchema.P_Exec_Self_MySchema_MyUser;

Revert;

-----------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------

If Object_ID('dbo.P_1','P') Is Not Null Drop Proc dbo.P_1;

Go

Create Proc dbo.P_1 As

Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];

Go

---------------------------------------------------------------------------------------------------------------------------------

If Object_ID('dbo.P_2','P') Is Not Null Drop Proc dbo.P_2;

Go

Create Proc dbo.P_2 With Execute As Caller As

Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];

Exec dbo.P_1;

Go

---------------------------------------------------------------------------------------------------------------------------------

If Object_ID('dbo.P_3','P') Is Not Null Drop Proc dbo.P_3;

Go

Create Proc dbo.P_3 With Execute As 'MyUser' As

Select Schema_Name() [Schema_Name],Object_Name(@@ProcID) [Proc],Current_User [Current_User],System_User [System_User];

Exec dbo.P_2;

Go

Grant Exec On dbo.P_3 To MyUser;

Go

---------------------------------------------------------------------------------------------------------------------------------

Exec P_3;

Execute As User='MyUser';

Exec P_3;

Revert;