permission

Question

Hi,
How do I make the users group i.e. domainname\users to be able to only select from tableA but user1 and user2 to have full control on it?
Note that user1 and user2 are already in domainname\users
Thanks

Answer 1

Hello arkiboys,

Do you mean you have a Windows Domain group "users" used for logon to SQL Server and a part of the members should have full control and the other part not?

The best would be to split the group into 2 groups and define the permission per group.

Otherwise you would have to grant full control permissions to the group and deny (deny goes for grant) permission for those user, who shall not have full control; a (sorry very) ugly solution.

---

Olaf Helper

Blog Xing

Answer 2

You can do this as follow:

• Grant DOMAINNAME\USERS group SELECT permissions on the database.
• Grant user1 and user2 control rights on TableA

This can be done with the following code:

use [YourDatabase]
GO
GRANT CONTROL ON [dbo].[TableA] TO [Domain\User1]
GO
GRANT CONTROL ON [dbo].[TableA] TO [Domain\User2]
GO
GRANT SELECT ON [dbo].[TableA] TO [DomainName\Users]
GO

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 3

You are saying that you have 2 users user1 & user2 having fill control + both users are the part of one domain user having select permissions only.

In this case, your users will get full permissions on table.

You must create separate users for separate  access rights requirements.

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.