You have to understand the problem before proceeding with the solution.
When you select from a view the SQL Server system would check permissions twice:
Once when you select from the view, and once when the view makes reference to the underlying table.
Obviously the second check would fail if the user has no permission on the underlying table.
Microsoft have solved this with "Ownership Chaining" (known as OC).
OC will bypass the permission check that would be done when the view is referencing the table,
Only if the owner of the view is the same as the owner of the table.
The thing is, OC is bypassing permission checks completely, which means that it can bypass denies as well...
For example, if "JoeBlogs" has the create view
permission, he can create a new view which has access to the entire table.
I suggest you read about ownership chaining before you decide your course of action.
books online
msdn blog
detailed example, using a stored procedure instead of view
Having said that, the first Intuitive solution would be to create the view under the same non-dbo schema as the underlying tables (or a schema owned by the same user as the non-dbo schema).
However, if you find that solution to be too risky there is another (and maybe even better) option:
You can always use a function (Multistatement Table-valued Function) with an EXECUTE AS
clause:
Create function syntax
execute as clause
This method will allow you to select from the function (execute
permission on the function) while the function belongs to dbo
. The user specified in the execute as
clause must have permissions on the underlying tables.
If you want users to select from the view, why are you granting to the table? By "revoke" do you mean explicitly revoke/deny? Deny will override grant so there's your problem... you should be able to accomplish this by adding grant to the view and not doing anything either way on the tables.
Here's a quick example where SELECT
has not been explicitly granted on the table, but has been on the view. The user can select from the view but not the table.
CREATE USER foo WITHOUT LOGIN;
GO
CREATE TABLE dbo.a(id INT);
CREATE TABLE dbo.b(id INT);
GO
CREATE VIEW dbo.v
AS
SELECT a.id FROM a INNER JOIN b ON a.id = b.id;
GO
GRANT SELECT ON dbo.v TO foo;
GO
EXECUTE AS USER = N'foo';
GO
-- works:
SELECT id FROM dbo.v;
GO
-- Msg 229, SELECT denied:
SELECT id FROM dbo.a;
GO
REVERT;
Note that this assumes foo
has not been granted elevated privileges through explicit permissions on the schema or database, or through role or group membership.
Since you are using tables in multiple databases (sorry I missed the end of that first sentence initially), you also may need explicit grants on the table(s) in the database where the view does not exist. In order to avoid granting select to the table(s), you could create a view in each database, and then join the views.
Create two databases and a login:
CREATE DATABASE d1;
GO
CREATE DATABASE d2;
GO
USE [master];
GO
CREATE LOGIN blat WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
In database d1
, create a user, then create a table and a simple view against that table. Grant select to the user only against the view:
USE d1;
GO
CREATE USER blat FROM LOGIN blat;
GO
CREATE TABLE dbo.t1(id INT);
GO
CREATE VIEW dbo.v1
AS
SELECT id FROM dbo.t1;
GO
GRANT SELECT ON dbo.v1 TO blat;
GO
Now, in the second database, create the user, then create another table and a view that joins that table to the view in d1
. Grant select only to the view.
USE d2;
GO
CREATE USER blat FROM LOGIN blat;
GO
CREATE TABLE dbo.t2(id INT);
GO
CREATE VIEW dbo.v2
AS
SELECT v1.id FROM dbo.t2
INNER JOIN d1.dbo.v1 AS v1
ON t2.id = v1.id;
GO
GRANT SELECT ON dbo.v2 TO blat;
GO
Now launch a new query window and change the credentials to be for the login blat
(EXECUTE AS
does not work here). Then run the following from the context of either database, and it should work fine:
SELECT id FROM d1.dbo.v2;
These should both yield Msg 229 errors:
SELECT id FROM d1.dbo.t1;
GO
SELECT id FROM d2.dbo.t2;
Results:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 't1', database 'd1', schema 'dbo'.
Msg 229, Level 14, State 5, Line 3
The SELECT permission was denied on the object 't2', database 'd2', schema 'dbo'.
Best Answer
The "secret" here is that your
login
test issysadmin
and he's mapped todbo
in everydatabase
.Test user
that is on your picture is NOT theuser
corresponding to login "test".If you want to execute under user
test
you should write the codeAnd this user will have the deny on
customer
table.But now all the code you executed you executed it under
dbo
user (that corresponds totest
login), this can be proved by executingthat will show
test
assystem_user
anddbo
asuser
.This situation can happen if the login (test) was mapped to this database when he was not
sysadmin
, sotest
user was created. But then someone made himsysadmin
and now he is mapped to dbo andtest
is just orphaned.Another situation is when
databas
e was restored and thatuser
(still orphaned) has no corresponding login, the newtest
login is not related to it and is mapped todbo
This way you make him an ordinary login.
Then you should check if your user test is his corresponding login or just re-map it:
UPDATE
Assuming that the first screenshot is shown "as is" it seems that the code to test "Check if it still works" was executed under the same account that denied the
select
permission, i.e. undersa
login as there is no code impersonatingtest
login. This explains because theselect
code was executed without errors and also the result ofselect system_user, user
confirms this.