I'm trying to implement shared API in MS SQL Server 2014 DB. In that architecture, schemas should have similar structures and use shared API owned by dbo whereas at the same time exposes own API. To call one another without qualifying object names, EXECUTE AS USER statement is used for context switching to a certain default schema of the current user.
The problem is here: while immediate access with user context switching works fine (e.g. EXECUTE AS USER followed by SELECT * from test_tbl;), the access through default schema in a stored procedure fails with error Msg 208, Level 16, State 1.
Before posting my question, I tried a lot of experiments and tests and searched MSDN, Web and SQL forums for any clue during several days with no luck.
Scripts for reproducing (<MDF> and <LDF> requires substitutions with appropritate file paths):
-- DB creation
CREATE DATABASE [test_sql]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'test_sql', FILENAME = N'<MDF>' , SIZE = 5120KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'test_sql_log', FILENAME = N'<LDF>' , SIZE = 2048KB , FILEGROWTH = 10%)
COLLATE Cyrillic_General_CI_AS
GO
ALTER DATABASE [test_sql] SET COMPATIBILITY_LEVEL = 120
GO
ALTER DATABASE [test_sql] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [test_sql] SET ANSI_NULLS OFF
GO
ALTER DATABASE [test_sql] SET ANSI_PADDING OFF
GO
ALTER DATABASE [test_sql] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [test_sql] SET ARITHABORT OFF
GO
ALTER DATABASE [test_sql] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [test_sql] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [test_sql] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [test_sql] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [test_sql] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [test_sql] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [test_sql] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [test_sql] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [test_sql] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [test_sql] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [test_sql] SET DISABLE_BROKER
GO
ALTER DATABASE [test_sql] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [test_sql] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [test_sql] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [test_sql] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [test_sql] SET READ_WRITE
GO
ALTER DATABASE [test_sql] SET RECOVERY FULL
GO
ALTER DATABASE [test_sql] SET MULTI_USER
GO
ALTER DATABASE [test_sql] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [test_sql] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
ALTER DATABASE [test_sql] SET DELAYED_DURABILITY = DISABLED
GO
USE [test_sql]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [test_sql] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
-- Srv login, DB user and schema creation
CREATE LOGIN [test_usr_login] WITH PASSWORD=N'test_usr_login', DEFAULT_DATABASE=[test_sql], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE USER [test_usr] FOR LOGIN [test_usr_login] WITH DEFAULT_SCHEMA=[test_schema]
GO
CREATE SCHEMA [test_schema] AUTHORIZATION [test_usr]
GO
-- Table and stored proc creation
IF OBJECT_id("[test_schema].[test_tbl]", "U") IS NOT NULL
DROP TABLE [test_schema].[test_tbl];
GO
CREATE TABLE [test_schema].[test_tbl](
[tc] [nchar](10) NULL
) ON [PRIMARY]
GO
IF OBJECT_id("[dbo].[TA]", "P") IS NOT NULL
DROP PROCEDURE [dbo].[TA];
GO
CREATE PROCEDURE [dbo].[TA] AS BEGIN
SET NOCOUNT ON;
SELECT * FROM
(VALUES
('CURRENT_USER', CURRENT_USER),
('SCHEMA_NAME', SCHEMA_NAME()),
('have_UNqualified_select', cast(HAS_PERMS_BY_NAME("[test_tbl]", "OBJECT", "SELECT") as nchar(10))),
('have_qualified_select', cast(HAS_PERMS_BY_NAME("[test_schema].[test_tbl]", "OBJECT", "SELECT") as nchar(10)))
) AS tmptbl([key], val); -- select permissions fro [test_tbl] of the current user
SELECT tc as qualified_tc FROM [test_schema].[test_tbl]; -- qualified select
SELECT tc as UNqualified_tc from [test_tbl]; -- unqualified select fails with Msg 208
END
GO
GRANT EXECUTE ON [dbo].[TA] TO [test_usr]
GO
Test script:
USE [test_sql]
GO
DECLARE @return_value int
execute as login = N'test_usr_login'; -- even when logged in with test_usr_logn, Msg 208 occurs
EXEC @return_value = [dbo].[TA]
revert
SELECT 'Return Value' = @return_value
GO
Output message:
Msg 208, Level 16, State 1, Procedure TA, Line 14 Invalid object name 'test_tbl'.
(1 row(s) affected)
Output result:
key val
CURRENT_USER test_usr
SCHEMA_NAME test_schema
have_UNqualified_select 1
have_qualified_select 1
I would appreciate anyone who could bring light to the solution to the problem described.
The problem is here: while immediate access with user context switching works fine (e.g. EXECUTE AS USER followed by SELECT * from test_tbl;), the access through default schema in a stored procedure fails with error Msg 208, Level 16, State 1.
The problem here is that you don't know how SQL Server resolves non qualified object name.
When you execute plain sql and use an object without specifying its schema, first user default schema is checked, if object is not found, dbo schema is checked. If the object is not found even in dbo, the error is raised.
It's different when it comes to stored procedure. If schema is not specified, sp's schema is checked first, if object is not found, dbo schema is checked, if it's not found again the error is raised. User default schema is never checked in case of stored procedure