Can user view objects they only have select permission on?

Can user view objects they only have select permission on?

Post by U3Bhbmt5QV » Fri, 30 Nov 2007 00:42:03


I have a user that belongs to a role. This role only has select permissions
on 10 views. When I log in as this user via Management Studio I cannot see
the views however I can execute queries against them.

On another server that I did not setup, that I'm supposed to be mimicking
the same security, this same user can see the views.

Any ideas what the difference is?

Thanks!
 
 
 

Can user view objects they only have select permission on?

Post by U3Bhbmt5QV » Fri, 30 Nov 2007 07:00:01

Here's the commands that I'm executing in order:

CREATE ROLE [Customers_ROLE] Authorization dbo
CREATE SCHEMA [Customers_Schema] AUTHORIZATION [Customers_Role] Deny View
Definition to [Customers_Role]
exec sp_adduser 'phenson', 'phenson', [Customers_Role]
GRANT SELECT ON [dbo].[PT_VIEW] TO [Customers_Role]


When I log in as phenson I do not see PT_View but I can query on it. I need
to be able to see it.

 
 
 

Can user view objects they only have select permission on?

Post by U3Bhbmt5QV » Sat, 01 Dec 2007 01:58:00

I thought I'd answer my own question for those of you who come across this
some day. I need to remove the "Deny View Definition" portion and that took
care of it. The user was able to see that view and execute it but could not
see the script.
 
 
 

Can user view objects they only have select permission on?

Post by Erland Som » Sat, 01 Dec 2007 07:24:51

SpankyATL ( XXXX@XXXXX.COM ) writes:

Why then did you do DENY VIEW DEFINITION to a role that you made phenson a
member of?


--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/
 
 
 

Can user view objects they only have select permission on?

Post by U3Bhbmt5QV » Sat, 01 Dec 2007 15:11:01

I was working from a script that was given to me. The person who developed
it mistakenly thought deny view would only deny the user from viewing the
source code.