Read only and access to specific databases objects

  • Hi,

    Solution required on...............

    Can we give specific objects (i.e. Tabel, View, Procedure etc....) access to a user in SQL Server 2005?

    If yes then How?

    I do not I want windows based authentication, I need SQL Server Authentication.

    If a user has got access to table1, table2 as read only rights then I need whenever he/she loggin in database using SSMS, he/she should see only table1, table2 rest other objects should not be displayed to him/her. OR he/she can only be able to SELECT on table1, table2.

    Thanks in advance,

    Regards

    Sadanand

  • Probably want to create a view so that they are limited to the tables you specify...then you could create a new database roll add the user to the role and select the appropriate tables/views/objects what have you that your user needs select permission for. Make sense?

  • For a user, SSMS will only list those objects where there is some privilege on that object.

    Recommend is to create a database role, grant/deny privileges to the database role and then assign database users to the database role.

    SQL = Scarcely Qualifies as a Language

  • Use grant / revoke statements to control specific access writes.

    CREATE ROLE SELECT_ONLY;

    GRANT SELECT ON TABLE1,TABLE2 TO SELECT_ONLY;

    EXEC sp_addrolemember 'SELECT_ONLY', 'USER1';

    EXEC sp_addrolemember 'SELECT_ONLY', 'USER2';

    Oded

    www.dbsnaps.com

    www.orbiumsoftware.com

Viewing 4 posts - 1 through 3 (of 3 total)

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