Grant Specific Access to User

Question

Grant Specific Access to User

balasach82

SSCrazy Eights

Points: 9653
More actions
August 4, 2010 at 3:24 am

#225648

In a DB, A user needs to have access to create new tables, but for certain tables that user should have only read permissions(no modifications). In What ways this can be done?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 1

In a DB, A user needs to have access to create new tables, but for certain tables that user should have only read permissions(no modifications). In What ways this can be done?

Hi,

Go through Server Rolse & User Mapping concepts.

Create DDL trigger as per your requirements.

Cheers!

Sasidhar Pulivarthi

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 2

To create tables, I can give ddl_admin access (DB role) to the user. But, then he would have full access to those tables for which he should have only read access. How to go about it?

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 3

If this is a new database that you are designing, I would strongly recommend the use of schemas. With the user-schema isolation coming in from SQL 2005, the task at hand will become much simpler (the user can have modification rights for a particular schema, but on the other, they can only have read rights).

If this is a pre-existing database, the only option I have the use of triggers (never done before :))

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 4

You can give object level permission to a user..

You can give only select on tables

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 5

I am having 2 sets of users. One set(say A) should have only read-access to all the tables in a db. The other set (say B) should have permissions to create tables/views etc and read-access to all the tables in the db.

Will the below option work?

1. Giving db_datareader access to users (A), so that they can only use SELECT on all the tables.

2. Giving DDL_ADMIN to (B), and using the below script

DENY INSERT ON OBJECT::Test TO ;

GO

DENY UPDATE ON OBJECT::Test TO ;

GO

DENY DELETE ON OBJECT::Test TO ;

GO

--getting "Incorrect syntax near 'DROP'" for below part

DENY DROP ON OBJECT::Test TO ;

GO.