vrxts5ef

1-- DB creation
2CREATE DATABASE [test_sql]
3 CONTAINMENT = NONE
4 ON  PRIMARY 
5( NAME = N'test_sql', FILENAME = N'<MDF>' , SIZE = 5120KB , FILEGROWTH = 1024KB )
6 LOG ON 
7( NAME = N'test_sql_log', FILENAME = N'<LDF>' , SIZE = 2048KB , FILEGROWTH = 10%)
8 COLLATE Cyrillic_General_CI_AS
9GO
10ALTER DATABASE [test_sql] SET COMPATIBILITY_LEVEL = 120
11GO
12ALTER DATABASE [test_sql] SET ANSI_NULL_DEFAULT OFF 
13GO
14ALTER DATABASE [test_sql] SET ANSI_NULLS OFF 
15GO
16ALTER DATABASE [test_sql] SET ANSI_PADDING OFF 
17GO
18ALTER DATABASE [test_sql] SET ANSI_WARNINGS OFF 
19GO
20ALTER DATABASE [test_sql] SET ARITHABORT OFF 
21GO
22ALTER DATABASE [test_sql] SET AUTO_CLOSE OFF 
23GO
24ALTER DATABASE [test_sql] SET AUTO_SHRINK OFF 
25GO
26ALTER DATABASE [test_sql] SET AUTO_CREATE_STATISTICS ON
27GO
28ALTER DATABASE [test_sql] SET AUTO_UPDATE_STATISTICS ON 
29GO
30ALTER DATABASE [test_sql] SET CURSOR_CLOSE_ON_COMMIT OFF 
31GO
32ALTER DATABASE [test_sql] SET CURSOR_DEFAULT  GLOBAL 
33GO
34ALTER DATABASE [test_sql] SET CONCAT_NULL_YIELDS_NULL OFF 
35GO
36ALTER DATABASE [test_sql] SET NUMERIC_ROUNDABORT OFF 
37GO
38ALTER DATABASE [test_sql] SET QUOTED_IDENTIFIER OFF 
39GO
40ALTER DATABASE [test_sql] SET RECURSIVE_TRIGGERS OFF 
41GO
42ALTER DATABASE [test_sql] SET  DISABLE_BROKER 
43GO
44ALTER DATABASE [test_sql] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
45GO
46ALTER DATABASE [test_sql] SET DATE_CORRELATION_OPTIMIZATION OFF 
47GO
48ALTER DATABASE [test_sql] SET PARAMETERIZATION SIMPLE 
49GO
50ALTER DATABASE [test_sql] SET READ_COMMITTED_SNAPSHOT OFF 
51GO
52ALTER DATABASE [test_sql] SET  READ_WRITE 
53GO
54ALTER DATABASE [test_sql] SET RECOVERY FULL 
55GO
56ALTER DATABASE [test_sql] SET  MULTI_USER 
57GO
58ALTER DATABASE [test_sql] SET PAGE_VERIFY CHECKSUM  
59GO
60ALTER DATABASE [test_sql] SET TARGET_RECOVERY_TIME = 0 SECONDS 
61GO
62ALTER DATABASE [test_sql] SET DELAYED_DURABILITY = DISABLED 
63GO
64USE [test_sql]
65GO
66IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [test_sql] MODIFY FILEGROUP [PRIMARY] DEFAULT
67GO
68
69-- Srv login, DB user and schema creation
70CREATE LOGIN [test_usr_login] WITH PASSWORD=N'test_usr_login', DEFAULT_DATABASE=[test_sql], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
71GO
72CREATE USER [test_usr] FOR LOGIN [test_usr_login] WITH DEFAULT_SCHEMA=[test_schema]
73GO
74CREATE SCHEMA [test_schema] AUTHORIZATION [test_usr]
75GO
76
77-- Table and stored proc creation
78IF OBJECT_id("[test_schema].[test_tbl]", "U") IS NOT NULL
79DROP TABLE [test_schema].[test_tbl];
80GO
81CREATE TABLE [test_schema].[test_tbl](
82    [tc] [nchar](10) NULL
83) ON [PRIMARY]
84GO
85
86IF OBJECT_id("[dbo].[TA]", "P") IS NOT NULL
87DROP PROCEDURE [dbo].[TA];
88GO
89CREATE PROCEDURE [dbo].[TA] AS BEGIN
90    SET NOCOUNT ON;
91  SELECT * FROM 
92  (VALUES
93  ('CURRENT_USER', CURRENT_USER),
94  ('SCHEMA_NAME', SCHEMA_NAME()),
95  ('have_UNqualified_select', cast(HAS_PERMS_BY_NAME("[test_tbl]", "OBJECT", "SELECT") as nchar(10))),
96   ('have_qualified_select', cast(HAS_PERMS_BY_NAME("[test_schema].[test_tbl]", "OBJECT", "SELECT") as nchar(10)))
97  ) AS tmptbl([key], val); -- select permissions fro [test_tbl] of the current user
98  SELECT tc as qualified_tc FROM [test_schema].[test_tbl]; -- qualified select
99  SELECT tc as UNqualified_tc from [test_tbl]; -- unqualified select fails with Msg 208
100END
101GO
102
103GRANT EXECUTE ON [dbo].[TA] TO [test_usr]
104GO
105	
106USE [test_sql]
107GO
108DECLARE @return_value int
109execute as login = N'test_usr_login'; -- even when logged in with test_usr_logn, Msg 208 occurs
110EXEC    @return_value = [dbo].[TA]
111revert
112SELECT  'Return Value' = @return_value
113GO
114	
115key val
116CURRENT_USER    test_usr
117SCHEMA_NAME test_schema
118have_UNqualified_select 1         
119have_qualified_select   1