read access to a view only

Question

Post reply

read access to a view only

petitpere

SSCrazy

Points: 2520
More actions
October 27, 2009 at 7:48 am

#205927

Hi all,
Maybe a stupid question for you experts, but I'm not much into security, so ...
Is it possible, into SQL2005, to grant read access rights to a given user to one view only in a given DB?
Thanks for your answer
pp

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

petitpere SSCrazy Points: 2520 More actions · Answer 1

What if I create a user U1,

then I create a schema S1 to which I attach the user U1 with "deny all" permissions,

then, in the Properties of the specific view V1, under Permissions I create the user U1 with Select grant rights.

Does it make sense?

petitpere SSCrazy Points: 2520 More actions · Answer 2

Hi,

I'll really appreciate if someone could help me on this one.

My aim is to grant reader rights to one specific user to only one view.

So I wrote and ran following script:

create view z_rp107 as select * from T1
go
create login zl1 with password = 'xxx'
go
create schema zs1 authorization dbo
go
create user zu1 for login zl1 with default_schema = zs1
go
grant select on v_rp107 to zu1
go

table T1 exists, sql server 2005

Logging in with the login I created, I can query the view z_rp107, but every other table as well!???!

Following script works fine:

select * from v_rp107 or select * from dbo.v_rp107

but following doesn't work

select * from zs1.v_rp107 or select * from zu1.v_rp107

What am I doing wrong?

Any help will be much appreciated ...

Thanks

petitpere SSCrazy Points: 2520 More actions · Answer 3

Added

EXEC sp_addrolemember 'db_reader', 'zu1'

to my code, but it doesn't help

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Here is an excellent article by Brian Kelly which may help you to accomplish what you want

http://www.sqlservercentral.com/articles/Security/sqlserversecurityfixeddatabaseroles/1231/

It contains examples and very good explanations and how to information

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

petitpere SSCrazy Points: 2520 More actions · Answer 5

Thank you bitbucket for your answer.

Unfortunatly the article relates to sql server 2000 and excludes the schemas of sql 2005.

It is however helpful on some issues ... thank you

petitpere SSCrazy Points: 2520 More actions · Answer 6

SOLVED ...

the problem was with the database roles granted

thank you very much