October 29, 2012 at 6:10 pm
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 ?
October 29, 2012 at 10:02 pm
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
October 30, 2012 at 7:55 pm
How can I investigate if I have Deny permissions set somewhere ?
October 31, 2012 at 11:36 am
Read through this: http://msdn.microsoft.com/en-us/library/ms176097.aspx. I think example H is what you are looking for.
October 31, 2012 at 2:22 pm
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
October 31, 2012 at 2:30 pm
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