user permissions to only for view

  • 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..??

  • use master;

    GO

    DENY VIEW ANY DATABASE TO james

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

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

  • 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.

  • 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..

  • 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

  • $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.

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

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

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