Security Permission

Question

Post reply

Security Permission

New2SQL-343122

Default port

Points: 1461
More actions
September 2, 2008 at 6:00 am

#127253

Hi All
Created Supervisor group in the Active Directory and added 4 person in that group. (AA,BB,CC,DD) - Windows Authentication users
Later, I have Added AD group Supervisor under Server->Security->Logins
This Supervisor group has db_owner and public database role Permission.
Among these 4 person, 1 (AA) should have R/W person and rest should have Read Permission only.
Please help me to implement when I am in this situation.
Regards,
Mohanraj Jayaraman

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

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

Shabba SSCertifiable Points: 5007 More actions · Answer 1

Among these 4 person, 1 (AA) should have R/W person and rest should have Read Permission only

Create a 2nd AD group or seperate SQL login for 'AA' with the appropriate permissions

Mark

Shabba SSCertifiable Points: 5007 More actions · Answer 2

What do you mean by R/W permission? You have already given those users dbo access? What do they need to do that dbo won't give them?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

Mohanraj (9/2/2008)
Hi All
Created Supervisor group in the Active Directory and added 4 person in that group. (AA,BB,CC,DD) - Windows Authentication users
Later, I have Added AD group Supervisor under Server->Security->Logins
This Supervisor group has db_owner and public database role Permission.
Among these 4 person, 1 (AA) should have R/W person and rest should have Read Permission only.
Please help me to implement when I am in this situation.
Regards,
Mohanraj Jayaraman

it looks like you are assigning too many permissions for these users, if they have differing permissions requirements then you would need separate groups too

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

New2SQL-343122 Default port Points: 1461 More actions · Answer 4

Hi

Thanks for the reply.

R/W mean for Readwrite.

Name of the Active Directory Group "Quality" under a domain.

Under the Quality group AA, BB, CC, DD are added.

Whereas, Server->Database->Security->Login->Domain\Quality has following database role permission

db_owner

public

Now, I want to give permission for BB, CC, DD as Readonly and for AA should be ReadWrite.

This moment I cannot split the AD group for rights basis because application need db_owner permission for all the 4 users and in the database perspective I want for the BB, CC, DD user should be readonly role and AA to be readwrite.

Due to this reason I have added 4 users together into a single group.

Tried using the below Tsql for avoid this situation.

Deny insert, delete on dbo. to [Domain\BB]

Deny insert, delete on dbo. to [Domain\CC]

Deny insert, delete on dbo. to [Domain\DD]

After executing the above command, the windows auth. users could able to select the table records when they are at Management Studio.

Regards,

Moharaj Jayaraman

New2SQL-343122 Default port Points: 1461 More actions · Answer 5

Hi Marc,

Even, I thought of doing the same thing, but in application perspective reader permission user needed to have db_owner rights. Keeping in mind, added all these four users into single group and in the sql server lever I want to protect them.

Tried using by following commands and doesn't helped me.

Deny insert, delete on dbo. to domain\BB

Whereas, the BB user already existing into Supervisor group which has db_owner and public rights.

Doesn't know, how to protect this windows authentication users by user level.

Thanks for any reply/suggestion.

Regards,

Mohanraj Jayaraman

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

denying select and insert should stop the users opening the objects, are you sure they havent been given sysadmin server role?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

New2SQL-343122 Default port Points: 1461 More actions · Answer 7

Yeah, they are not sysadmin.

Regards,

Mohanraj Jayaraman