user permissions to only for view

  • I need to give user 'dhii' select permissions only to a view not for all tables. The view has columns from two tables from the same database (ABC). 'dhii' should only see the columns in this view, not any underlying tables.

    what are the steps to follow?

  • You should give the user only SELECT permission on the view.

    GRANT SELECT ON viewname TO username;

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Use the below query:

    Use [Databasename]

    go

    Grant SELECT ON Viewname TO dhii;

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

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

    You'll need to check which permissions to user already has and then deny them.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Granting SELECT on a view does exactly that. It doesn't revoke any other permissions that the user already has. Query sys.database_permissions to find out what those permissions are.

    John

  • it has connect,select,execute permissions only..

  • Is this user in any of the Database Roles? If so remove it, and it will have only the permission you explicity added.

  • it doesn't have any permission on any other database...i want to disable security,managment to that user

    please check the attachment user can see 'sa'(i want to restrict it)

  • $w@t (10/10/2013)


    it doesn't have any permission on any other database...i want to disable security,managment to that user

    please check the attachment user can see 'sa'(i want to restrict it)

    Just because he can see sa doesn't mean he can do anything with it.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • can't i make it disable...and other databases also...though he is not able to acess i want to make them disable..is it possible?

  • $w@t (10/10/2013)


    can't i make it disable...and other databases also...though he is not able to acess i want to make them disable..is it possible?

    You can hide the databases if you really must. But not sure about the sa login.

    You can always disable sa of course.

    How to hide SQL Server user databases in SQL Server Management Studio[/url]

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • THank you all..

    how far is it secured..like if i give access with username to another client with public IP then does it safe for our data..??

    how can i acheive high security in this process

  • I am bit confusing on this process...

    Can anyone clarify that i just want a user to see only one view in specific database..rest of all will be hide from user even systemdatabases ,logins ,another databases

    Thanku you very much..

  • Try it for yourself. Create a new login with the restricted permissions and log on as it. See what you can see and see what you can do. It may be that there are some objects that are always displayed and you just have to live with it. Bear in mind that the user will be able to see all objects that the public role has any permissions on.

    John

Viewing 15 posts - 1 through 15 (of 22 total)

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