12

I have a SQL Server 2012 instance with a few databases. In one of them I created a view, that selects tables in more than a database.

I want a user to be able to select that view, but it must not select its tables. The view was created exactly because the user can't select the tables.

I've read https://stackoverflow.com/questions/368414/grant-select-on-a-view-not-base-table and http://msdn.microsoft.com/en-us/library/ms188676.aspx and still it is not working.

If I do a GRANT SELECT TABLE TO USER to all tables, the user is able to select the view. But if I revoke to any table, it fails.

This should be a easy procedure to do, but I'm having trouble to make it work. I've seen it happen before (the owner of a instance gave me access to a view and didn't do it to its tables) but I'm unable to do it or find somebody who knows how.

Could somebody provide me a tutorial on how to do it, or a code example?


When the user SELECTs the view I get the message:

The SELECT permission was denied on the object <TABLE>, database <DB>, schema dbo.

If I grant select to that table, the error message changes the table name to another table the view reads.

1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Aug 29, 2017 at 9:47

3 Answers 3

22

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'.

0
1

Community wiki answer originally added to the question by its author:

This is what I did:

  1. Created a view in DB A, joining all tables in it.
  2. Granted SELECT access to the user on that view, and NOT to any of its tables. User was successfully able to query the view and not the tables.
  3. Created a view in DB B, joining tables in this DB together with the view in DB A.
  4. Granted SELECT access to the user on this second view, and also NOT to any table. User was successfully able to query this final view and see data.

I think it's odd a view is able to query tables in its DB that the user doesn't have direct access but is unable to do it in tables from other DB. At least it worked.

0

If you activate Cross database ownership chaining for the server than cross database views will work fine.

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-cross-database-access-in-sql-server

mind the risks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.