1

I have a table that they put permissions on. So I cant see social security number and DOB. I agree with this setup, but has been creating a huge headache for the other users as they can no longer do a select * against the table. “The SELECT permission was denied on the column”

Can anyone think of a way to run a query that will only select the columns you have permission to view?

I know it will be a messy version of select *, but the permissions are only going to get worse and the complaints are only going to get worse. I'm ok with slightly complex as notes/directions can be added.

user1654753
  • 21
  • 2
  • Do you want to generate the query to select those columns, or do you want to know how to discover which columns are selectable? – Iain Samuel McLean Elder Jan 14 '14 at 17:39
  • Consider giving the users a view that exposes just the columns they are allowed to see. Then the users can still write `SELECT * FROM view;` for convenience. – Iain Samuel McLean Elder Jan 14 '14 at 17:40
  • 1
    Which database engine are you using? – Iain Samuel McLean Elder Jan 14 '14 at 17:41
  • Similar to this question: http://stackoverflow.com/questions/413819/select-except – Steve Wellens Jan 14 '14 at 18:04
  • Well this will be effecting multiple databases(clients), and multiple tables. So ideally I don't want to have to create a view for each table. But if this one of the better ways then so be it, but it would have to be dynamic based on the user. Some users above my pay grade will be able to see DOB but not social security. And managers will be able to see all. Other tables will have other columns effected. We wont know until after the fact. I would like to have a query in place when that happens. Running SQL server 2005 currently will move over to sql 2012 at the end of the year. – user1654753 Jan 14 '14 at 18:43

0 Answers0