How to give a user ONLY permission to a view, and nothing else in the database

  • I need to give user Joe select permissions to a view. The view has columns from two tables from the same database (ABC). Joe should only see the columns in this view, not any underlying tables. I should mention that the two tables are owned by Dave.

    I thought by writing: "GRANT SELECT ON View1 TO Joe;"

    but its not working...I receive the following error;

    The SELECT permission was denied on the object 'tblReviews', database 'ABC', schema 'Dave'.

    What else can I do ?

  • Works fine here. You must have a DENY permission set somewhere.

    CREATE SCHEMA [Dave]

    GO

    CREATE USER [Joe] WITHOUT LOGIN

    GO

    CREATE TABLE [Dave].[Test1] (Col1 INT, Col2 INT, Col3 INT)

    GO

    INSERT INTO Dave.Test1

    VALUES (1,1,1),(2,2,3),(3,2,4)

    GO

    CREATE VIEW dbo.[Test1]

    AS

    SELECT Col1, Col2 FROM [Dave].[Test1]

    GO

    GRANT SELECT ON dbo.[Test1] TO Joe

    EXECUTE AS USER = 'Joe'

    SELECT * FROM [Dave].[Test1]

    --Msg 229, Level 14, State 5, Line 1

    --The SELECT permission was denied on the object 'Test1', database 'ABC', schema 'Dave'.

    SELECT * FROM dbo.[Test1]

    --Col1Col2

    --1 1

    --2 2

    --3 2

  • How can I investigate if I have Deny permissions set somewhere ?

  • Read through this: http://msdn.microsoft.com/en-us/library/ms176097.aspx. I think example H is what you are looking for.

  • See the following url for an Instance Security Audit script I wrote. It will tell you whether you have a DENY in your permissions for the database. Hope it helps.

    http://www.sqlservercentral.com/Forums/Topic1251262-146-1.aspx?Update=1

  • Thanks everybody for chiming in...I finally got it to work by this;

    Created the view

    Went to the view/properties/permissions/ in SMS..find the user that you want to have access...then choose grant for "select" under permissions...thats it.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply