We have a SQL Server 2008
database that contains two schema's. The owners of these schema's have been granted full SELECT
permissions to all tables within each others ownership.
I have created a user ID and have granted it access to only SQL Views
created in both of these schema's but not the underlying tables. In the example below I've created two views, one in each schema, that select data from one table in one of the schema's.
If I login as the schema owner (either testdata or testcntrl) it works regardless of the schema I execute the view from.
If I execute the View
as my new user in the schema that the same table exists in (in the example testdata) the select
is successful, but if I try from the other schema (testctrl) the select fails giving me the following permission error: The SELECT permission was denied on the object 'UserData', database 'DEV_DATABASE', schema 'TESTDATA'.
.
How does security propagate between schema's within a single database? Why is this successful from one schema but not the other?
Here is a rough example of what I am trying to do, please let me know if I can explain this better, as for Im not remotely close to a dba.
USE [DEV_DATABASE]
-- Create tables and insert dummy data
CREATE TABLE [DEV_DATABASE].[TESTDATA].[UserData]
(
COL1 CHAR(10),
COL2 CHAR(10),
COL3 CHAR(10),
COL4 CHAR(10),
COL5 CHAR(10)
);
INSERT INTO [DEV_DATABASE].[TESTDATA].[UserData] VALUES ('1','2','3','4',5');
INSERT INTO [DEV_DATABASE].[TESTDATA].[UserData] VALUES ('11','22','33','44',55');
CREATE TABLE [DEV_DATABASE].[TESTCTRL].[ControlData]
(
CTRL1 CHAR(10),
CTRL2 CHAR(10),
CTRL3 CHAR(10),
CTRL4 CHAR(10),
CTRL5 CHAR(10)
);
INSERT INTO [DEV_DATABASE].[TESTCTRL].[ControlData] VALUES ('a','b','c','d',e');
INSERT INTO [DEV_DATABASE].[TESTCTRL].[ControlData] VALUES ('aa','bb','cc','dd',ee');
-- Create views
CREATE VIEW [TESTDATA].[DATA_TEST_VIEW] AS
SELECT
COL1, COL2, COL3
FROM [TESTDATA].[UserData]
CREATE VIEW [TESTCTRL].[CTRL_TEST_VIEW] AS
SELECT
COL1, COL2, COL3
FROM [TESTDATA].[UserData]
-- permissions
DENY SELECT ON [TESTDATA].[UserData] to JOEUSER;
GRANT SELECT ON [TESTDATA].[DATA_TEST_VIEW] to JOEUSER;
GRANT SELECT ON [TESTCRL].[CTRL_TEST_VIEW] to JOEUSER;
EXECUTE AS USER = 'JOEUSER';
-- tests
SELECT * FROM [TESTDATA].[UserData];
-- fails as expected
SELECT * FROM [TESTDATA].[DATA_TEST_VIEW];
-- passes as expected
SELECT * FROM [TESTCRL].[CTRL_TEST_VIEW];
-- fails, and should pass?
REVERT;
Error message received:
SELECT * FROM [TESTCRL].[CTRL_TEST_VIEW];
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'UserData', database 'DEV_DATABASE', schema 'TESTDATA'.
Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object 'UserData', database 'DEV_DATABASE', schema 'TESTDATA'.