August 21, 2026 at 3:38 pm
Security has long been a problem for me. Every time I think I understand something, along comes another issue to show me that I don't.
I want to have an application on start-up query some information from a sort of status table, to see what it is allowed to do. I have a schema called RO into which I put all views and procedures that I want anyone to be able to view or execute. Any user has select and execute privileges on this entire schema. The procedure reads from a table, but obviously, I do not want to give all users permission to read the table directly.
When I have a new user run the procedure directly, he gets an error about permission to access the underlying table. I tried adding
execute as user = 'dbo'
into the procedure, and now the user gets error #15517, that he does not have permission. What is the point of EXECUTE AS if the user first needs some extra permission? I thought the whole point of such a command is that the AUTHOR of the procedure, who does have the necessary permission to read from the table, is allowing someone else to execute this procedure, which does only specific tasks, so that the less-privileged user may do certain things which his normal permissions do not cover.
I have studied tutorials on schemas, roles, logins, permissions and all of this until my brain is leaking out of my ears, and I still don't know what is going on. Can anyone clear this up for me?
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply