|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Friday, May 31, 2013 11:10 AM
Points: 70,
Visits: 225
|
|
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 ?
|
|
|
|
|
Right there with Babe
      
Group: General Forum Members
Last Login: Today @ 1:27 AM
Points: 758,
Visits: 1,103
|
|
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] --Col1 Col2 --1 1 --2 2 --3 2
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Friday, May 31, 2013 11:10 AM
Points: 70,
Visits: 225
|
|
| How can I investigate if I have Deny permissions set somewhere ?
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Yesterday @ 11:06 AM
Points: 36,
Visits: 416
|
|
| Read through this: http://msdn.microsoft.com/en-us/library/ms176097.aspx. I think example H is what you are looking for.
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Thursday, May 23, 2013 7:34 AM
Points: 189,
Visits: 864
|
|
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
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Friday, May 31, 2013 11:10 AM
Points: 70,
Visits: 225
|
|
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.
|
|
|
|