table access in sql server 2008

Question

Post reply

table access in sql server 2008

sql Anand

SSCommitted

Points: 1597
More actions
October 13, 2011 at 2:58 am

#249170

Hi,

i want to create 2 user(ie,sysadd1,sysadd2) in sql server 2008..

In that one db_name is account_db...

In that account_db i am having 50tables..

So that 1st user(sysadd1) can access only 1st 25 table in account_db..next user(sysadd2) can access next 25 tables account_db..

can we do like this in sql server..

if u knw plz tell me that solution..

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

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

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 1

Devendrakumar Shirbad

SSC-Forever

Points: 42493

October 13, 2011 at 3:23 am

#1393883

Create 2 schemas and place your tables accordingly. Then grant permissions to users for particular schema.

User-Schema Separation

http://msdn.microsoft.com/en-us/library/ms190387.aspx

sql Anand SSCommitted Points: 1597 More actions · Answer 2

sql Anand

SSCommitted

Points: 1597

October 13, 2011 at 3:54 am

#1393902

no no i want to do in single schema only..

my client want to use this 1 schema if i separate this into 2 schema means they cant understand this

so i cant create 2 schema separately

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 3

if i separate this into 2 schema means they cant understand this

Then you must take the pain (as a developer) and assign individual object level permissions to respective user.

It's not recommended for 2 reasons:

1. You require giving object level permissions for each newly created object to respective users. It would be never ending if you DB designs are not finalized.

2. In long run, if users of application increases then the trouble would be n-fold (assuming database roles are also not acceptable by your client :hehe:).