Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Read only and access to specific databases objects Expand / Collapse
Author
Message
Posted Thursday, June 11, 2009 6:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 4:47 AM
Points: 1, Visits: 8
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




Post #732974
Posted Thursday, June 11, 2009 7:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 20, 2010 9:56 AM
Points: 4, Visits: 419
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?
Post #733020
Posted Thursday, June 11, 2009 9:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 2,281, Visits: 4,228
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
Post #733123
Posted Thursday, June 11, 2009 9:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 10, 2009 11:23 AM
Points: 13, Visits: 31
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
Post #733138
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse