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->Database->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

Your Supervisor group should only have AA as a member. By making all of them members of db_owner (through membership of Supervisor) you have created a permissions problem that you cannot easily solve, since db_owner by definition has full permissions in the database.

You could drop Supervisor from the db_owner role, or drop BB, CC and DD from the Supervisor group. You'll need to assign the users to a Windows group/database role that has the appropriate permissions.

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 does't helped me.

 Deny insert, delete on dbo.<object> to domain\BB

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

 Donno, how to protect this windows authentication users by user level.

Thanks for any reply/suggestion.

Regards,
Mohanraj Jayaraman

It's not about domain or SQL authentication. Why does this read-only user have to have db ownership? Or why shouldn't this db owner have modify permissions? (Yes, those are two separate questions.)

The project was developed such a way the userlogin will have all the rights in addition i have joined by the time of went live.

They throw these requirement after the live.

Any suggestion would help me to fix this issue.