Sql Server Stored Proc Execute permissions--Execute As?
Posted on 2012-08-10
I'm a bit confused about permissions with sql-server and stored procs (sql 2008 R2).
I created a Logon "Test" and only gave him Public role, (which has not been altered). I added him as a user to a database.
So user Test has no select rights or anything on any tables in this database. I logged into SMS as Test and verified that I couldn't do a select or update or anything on these tables.
I then logged back on as admin, created a proc that updated a row in a table, and granted Execute permission to Test.
Logging back on as Test, I ran the proc and it ran successfully and updated the table. Shouldn't this have failed?
I looked in the procedure properties and the Execute As = Caller. So if I'm executing as Test, shouldn't this fail? Shouldn't I need to put a "With Execute As..." clause to allow the caller to update tables he normally does not have rights on?
Thanks for any clarification on this.