I'm assuming all the tables are in the dbo schema for my responses.

Hi Everyone, I am working on an application in which I have specific permissions needed. I have two separate tables, "Records" and "Confidential Records". Everyone has access to Records, but not everyone has access to Confidential Records.

Just for the sake of saying it. If these two tables have exactly the same table structure they should be 1 table and have a bit identifier as a boolean marking it confidential or not. I'll explain further in my next response.

I was planning on using an AD group to permission a stored procedure. I want everyone to be able to execute the Stored Procedure, but I only want records to be returned from the Confidential table for people who have permission.

You grant permissions to a schema not the tables. You could deny access to all the tables and create a "pub" (public) and "conf" (confidential) schema. These schemas would have views to the tables with select and execute permissions. In the case where you had a table with public and confidential records in a single table, you'd simply create the same view in public and confidential schemas but in the public schema you add where confidential = 0. This would make the view only return public records.

Is this easily possible? This is the back end for an .NET application and I do not want to have any errors... i'd just like to return an empty result set for the confidential table...

It is easy and should be non-interferring you just must make sure that the service account that the webserver is not a member of the ad groups that would have restrictions. This usually isn't the case.

Any help would be great, thanks!!

You are welcome to PM me if you want personal help setting this up.