Thanks to the OP's clarification of the error, I'm going to post my comment as a suggested answer.
In SQL Server, permissions are checked only when ownership changes.
Consider the following tables:
- Table
SchemaA.Table1
, owned by Alice.
- View
SchemaA.View1
, owned by Alice, depends on Schema1.Table1
.
- View
SchemaB.View1
, owned by Bob, depends on Schema1.View1
.
Consider these situations:
Alice attempts to select from SchemaA.Table1:
No change in ownership so permissions are not checked on the table. Alice is allowed access.
Alice attempts to select from SchemaA.View1:
No change in ownership so permissions are not checked on the view or on the table. Alice is allowed access.
Bob attempts to select from SchemaA.Table1:
There is a change in ownership on SchemaA.Table1 (Bob is attempting an operation on Alice's object) so Bob's permissions are checked on SchemaA.Table1.
Bob attempts to select from SchemaA.View1:
This is a change in ownership on SchemaA.View1 (Bob is attempting an operation on Alice's object) so Bob's permissions are checked on SchemaA.View1. There is no change in ownership from SchemaA.View1 to SchemaA.Table1 so Bob's permissions are not checked on SchemaA.Table1.
Bob attempts to select from SchemaB.View1.
There is no change in ownership on SchemaB.View1 (Bob is attempting an operation on Bob's object) so permissions are not checked on SchemaB.View1. There is a change in ownership from SchemaB.View1 to SchemaA.View1 so Bob's permissions (not Alice's) are checked on SchemaA.View1. There is no change in ownership from SchemaA.View1 to SchemaA.Table1 so Bob's permissions are not checked on SchemaA.Table1.
So, in the OP's sitation, the view [TESTCTRL].[CTRL_TEST_VIEW]
depends upon the table [TESTDATA].[UserData]
. The view and the table have different owners so there is a change in ownership (something some SQL Server books call a "break in the ownership chain"). To query the view, JoeUser needs to be granted the appropriate permissions to both the view and the table.
From the documentation (GRANT Schema Permissions):
GRANT SELECT, DELETE -- , etc.
ON SCHEMA::schema_name
TO user_name;
For certain permissions they need to be granted at both the database level and the schema level. For example, you can give a user CREATE TABLE
permissions at the database level, but that doesn't just allow them to create tables in any schema. Here is an example you can work through:
-- create a server-level SQL auth login
CREATE LOGIN DouglasExample WITH PASSWORD = 'x',
CHECK_POLICY = OFF;
-- create an empty database and add a schema
CREATE DATABASE Douglas;
GO
USE Douglas;
GO
CREATE SCHEMA foo;
GO
-- create a user linked to the login
CREATE USER DouglasExample FROM LOGIN DouglasExample;
GO
-- grant the user the ability to create tables
GRANT CREATE TABLE TO DouglasExample;
GO
-- grant them explicit permissions on the schema only
GRANT SELECT, INSERT, DELETE, ALTER, EXECUTE, CONTROL
ON SCHEMA::foo TO DouglasExample;
GO
-- now try to create tables in foo and in dbo
EXECUTE AS USER = N'DouglasExample';
GO
CREATE TABLE foo.what(id INT); -- succeeds
GO
CREATE TABLE dbo.who(id INT); -- fails, not in schema foo
-- CREATE TABLE who(id INT); would also fail with same error
-- unless DouglasExample had foo as their default_schema
GO
REVERT;
GO
The attempt in dbo fails with:
Msg 2760, Level 16, State 1
The specified schema name "dbo" either does not exist or you do not have permission to use it.
-- clean up:
USE master;
GO
DROP DATABASE Douglas;
GO
DROP LOGIN DouglasExample;
As a side note, please observe how important it is to always explicitly state the schema name when creating/referencing all objects.
Best Answer
In Oracle (and, realistically, most any reasonable database), everything is denied other than what is granted. So simply grant the user the privileges you want him to have