wzeAdn2X

1deny view definition to some_db_role
2	
3grant view definition on dbo.some_proc to some_db_role
4	
5grant view definition, references on symmetric key::some_key to some_db_role
6	
7USE master;
8GO
9IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
10BEGIN
11    DROP DATABASE TestPerms_20160921;
12END;
13GO
14CREATE DATABASE TestPerms_20160921;
15GO
16USE TestPerms_20160921;
17GO
18	
19IF OBJECT_ID('dbo.Test') IS NOT NULL
20DROP TABLE dbo.Test;
21CREATE TABLE dbo.Test
22(
23    TestID INT NOT NULL
24);
25GO
26IF OBJECT_ID('dbo.TestProc') IS NOT NULL
27DROP PROCEDURE dbo.TestProc;
28GO
29CREATE PROCEDURE dbo.TestProc
30AS
31BEGIN
32    SELECT 1;
33END
34GO
35	
36IF EXISTS (
37    SELECT 1 
38    FROM sys.database_principals dp 
39    WHERE dp.name = 'TestU'
40    )
41BEGIN
42    DROP USER TestU;
43END
44CREATE USER TestU WITHOUT LOGIN;
45
46IF EXISTS (
47    SELECT * 
48    FROM sys.database_principals dp 
49    WHERE dp.name = 'dbtestrole'
50    )
51BEGIN
52    DROP ROLE dbtestrole;
53END
54CREATE ROLE dbtestrole;
55
56ALTER ROLE dbtestrole ADD MEMBER TestU;
57	
58GRANT SELECT ON dbo.Test TO dbtestrole;
59GRANT EXECUTE ON dbo.TestProc TO dbtestrole;
60	
61EXECUTE AS USER = 'TestU';
62
63/* column names and types **are** visible */    
64SELECT s.name
65    , t.name
66    , c.name
67    , ty.name
68FROM sys.tables t
69    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
70    INNER JOIN sys.columns c ON t.object_id = c.object_id
71    INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
72WHERE s.name = 'dbo'
73    AND t.name = 'Test'
74ORDER BY s.name
75    , t.name
76    , c.name;
77
78/* access to view the definition is denied */
79EXEC sys.sp_helptext @objname = 'dbo.TestProc', @columnname = NULL;
80
81/* we can run the proc! */
82EXEC dbo.TestProc;
83
84REVERT;
85	
86DENY VIEW DEFINITION ON dbo.Test TO dbtestrole;
87
88EXECUTE AS USER = 'TestU';
89
90/* no rows returned as we can no longer view the table definition */
91SELECT s.name
92    , t.name
93    , c.name
94    , ty.name
95FROM sys.tables t
96    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
97    INNER JOIN sys.columns c ON t.object_id = c.object_id
98    INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
99WHERE s.name = 'dbo'
100    AND t.name = 'Test'
101ORDER BY s.name
102    , t.name
103    , c.name;
104
105/* we can still run the proc */
106EXEC dbo.TestProc;
107
108/* we can still see the content of the table */
109SELECT *
110FROM dbo.Test;
111
112REVERT;
113	
114USE master;
115GO
116IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
117DROP DATABASE TestPerms_20160921;