Home Forums SQL Server 2008 SQL Server 2008 - General How to give a user ONLY permission to a view, and nothing else in the database RE: How to give a user ONLY permission to a view, and nothing else in the database

  • 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