Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to give a user ONLY permission to a view, and nothing else in the database Expand / Collapse
Author
Message
Posted Monday, October 29, 2012 6:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 6, 2014 3:19 PM
Points: 72, Visits: 261
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 ?
Post #1378528
Posted Monday, October 29, 2012 10:02 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:07 PM
Points: 1,151, Visits: 1,591
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

Post #1378558
Posted Tuesday, October 30, 2012 7:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 6, 2014 3:19 PM
Points: 72, Visits: 261
How can I investigate if I have Deny permissions set somewhere ?
Post #1379128
Posted Wednesday, October 31, 2012 11:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:13 PM
Points: 41, Visits: 543
Read through this: http://msdn.microsoft.com/en-us/library/ms176097.aspx. I think example H is what you are looking for.
Post #1379462
Posted Wednesday, October 31, 2012 2:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:49 PM
Points: 191, Visits: 891
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
Post #1379525
Posted Wednesday, October 31, 2012 2:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 6, 2014 3:19 PM
Points: 72, Visits: 261
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.
Post #1379527
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse