sunny.tjk (11/1/2012)

---

opc.three (10/31/2012)

---

sunny.tjk (10/31/2012)

---

I can think of DENY SELECT but not sure how to implement it. I'd like to accomplish DENY SELECT to everyone except to the members of the approved group.

Here is one option:

1. Create a new Database Role named db_CompanyNameDefaultReader where CompanyName is your particular company name.

2. Add db_CompanyNameDefaultReader to db_datareader using sys.sp_addrolemember.

3. Add all relevant members of db_datareader to the new role db_CompanyNameDefaultReader again using sys.sp_addrolemember.

4. Remove all people now in db_CompanyNameDefaultReader from db_datareader using sys.sp_droprolemember.

5. Deny select on the columns in question from db_CompanyNameDefaultReader using the following DDL:

DENY SELECT ON OBJECT::[dbo].[TheTableName]([TheColumnName]) TO [db_CompanyNameDefaultReader];

I tried to implement this on my local but I received an error saying "Cannot use the special principal 'db_datareader'." when I executed the following code:

EXEC sp_addrolemember 'SSN_GRANTED', 'db_datareader'