EXECUTE as SELF

Question

EXECUTE as SELF

Steve Jones - SSC Editor

SSC Guru

Points: 734562
More actions
January 17, 2017 at 8:34 pm

#77490

Comments posted to this topic are about the item EXECUTE as SELF
January 17, 2017 at 10:27 pm

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Stewart "Arturius" Campbell - Tuesday, January 17, 2017 10:27 PM
interesting question, thanks Steve
Wonder where one would use this?

Nice, straightforward question - thanks, Steve. I use EXECUTE AS, but not generally SELF.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

George Vobr SSCrazy Eights Points: 9921 More actions · Answer 2

Nice one, thanks Steve. Who solved the Qotd 27.5.2016 shouldn't have a problem. Using
EXECUTE AS SELF is explained here: https://technet.microsoft.com/en-us/library/ms178106(v=sql.105).aspx

Mr. Brian Gale SSC-Insane Points: 24703 More actions · Answer 3

I liked that question. It was a fun one.
The only reason I can think to do an "EXECUTE AS SELF" thing would be if there was a table that only the DBA had read access on and the stored procedure could filter the data automatically on the username of the calling person. That way there is no risk of data visibility in the table. Mind you row level security would do a similar thing. Plus if the person who created or altered that stored procedure ever had their account locked or disabled (for example, they quit), that stored procedure will no longer run for anybody.<

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

webrunner SSC-Dedicated Points: 31731 More actions · Answer 4

Thanks for this question. I guessed SELF meant the user executing the procedure, so I learned something today.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

David Conn SSCertifiable Points: 5980 More actions · Answer 5

Thanks. Didn't know this for SQL Server. Similar concept to another platforam that I use.

sknox SSChampion Points: 12388 More actions · Answer 6

bmg002 - Wednesday, January 18, 2017 8:38 AM
I liked that question. It was a fun one.
The only reason I can think to do an "EXECUTE AS SELF" thing would be if there was a table that only the DBA had read access on and the stored procedure could filter the data automatically on the username of the calling person. That way there is no risk of data visibility in the table. Mind you row level security would do a similar thing. Plus if the person who created or altered that stored procedure ever had their account locked or disabled (for example, they quit), that stored procedure will no longer run for anybody.<

Surely EXECUTE AS OWNER would make more sense there, as you could have the same table protection without the reliance on the DBA's specific account.

Of course, you can even get that type of protection simply by granting no read permissions on the table at all -- ownership chaining will allow a stored procedure with the same owner as the table to access the table without specific rights on the table.