Can user view objects they only have select permission on?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SpankyATL - 28 Nov 2007 15:42 GMT

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!

Reply to this Message

SpankyATL - 28 Nov 2007 22:00 GMT

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.

> 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
[quoted text clipped - 6 lines]
>
> Thanks!

Reply to this Message

SpankyATL - 29 Nov 2007 16:58 GMT

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.

> Here's the commands that I'm executing in order:
>
[quoted text clipped - 17 lines]
> >
> > Thanks!

Reply to this Message

Erland Sommarskog - 29 Nov 2007 22:24 GMT

> Here's the commands that I'm executing in order:
>
[quoted text clipped - 6 lines]
> 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.

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

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

SpankyATL - 30 Nov 2007 06:11 GMT

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.

> > Here's the commands that I'm executing in order:
> >
[quoted text clipped - 9 lines]
> Why then did you do DENY VIEW DEFINITION to a role that you made phenson a
> member of?

Reply to this Message