user permissions to only for view

Question

Post reply

user permissions to only for view

Viewing 8 posts - 16 through 22 (of 22 total)

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

$t@r SSCommitted Points: 1751 More actions · Answer 1

I have given required permission with these commands

create login 'james' with password = 'xxx'

go

use MyDB

go

create user james for login james

go

grant select on [vw_paymen] t to james

after that

i have log on with james..he can see all database names and logins ( he can not able to access)..he can acess system databases..even user can query system databases..how can i restrict?? is it secured..??

Sean Pearce SSCoach Points: 15999 More actions · Answer 2

use master;

GO

DENY VIEW ANY DATABASE TO james

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

$t@r SSCommitted Points: 1751 More actions · Answer 3

if i run above query then the user can not see any databases including the database which he has acces to only view

dan-572483 SSCoach Points: 15870 More actions · Answer 4

I believe if you make this user Owner of the database that he should have access to, that overrides the deny view permission above and makes his database the only one visible, although that may give him more permissions on his database than you'd like.

$t@r SSCommitted Points: 1751 More actions · Answer 5

i want that user to just see only a view...if i make him as db_owner then he can do anything on database right??

but i don't want him to do anything other than select a view..

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

i have log on with james..he can see all database names and logins ( he can not able to access)..he can acess system databases..even user can query system databases

Is that a problem? As you said, he can't actually access those databases and logins. He may be able to query system databases, but he'll only see things he has permissions on. For example, he won't get a full result set from sys.server_principals. Like I said, he'll still be able to do everything that the public role can do.

how can i restrict?? is it secured..??

That depends on how secure you need it to be. If necessary, you can remove permissions from public, or you can revoke or deny server level permissions from james.

if i run above query then the user can not see any databases including the database which he has acces to only view

Does that matter? He can still run queries against the view. Do you actually need him to be able to see it in Object Explorer as well?

John

kevaburg SSCoach Points: 18131 More actions · Answer 7

$w@t (10/9/2013)
after login with taht user i can see systemtables,system stored procedures,another users(even i can modify it) under particular database...but i want to restrict that..

If you map the user to the database that contains the view only and add that view as a securable with SELECT permissions only, your requirements would be met.

sql-noob SSC-Addicted Points: 417 More actions · Answer 8

Kindly check what permissions/access you have given to the public role