Sql Server Stored Proc Execute permissions--Execute As?
Hi all,
I'm a bit confused about permissions with sql-server and stored procs (sql 2008 R2).
Example:
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.
--Jim
I'm a bit confused about permissions with sql-server and stored procs (sql 2008 R2).
Example:
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.
--Jim
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much, that explains it! Is this something that changed from sql-2000? I ran into this because I'd swear at some point (long ago) I had an application in which I had to grant all users access to all tables that would be updated in sp's. Since then I'd gotten in the habit of having the website's connections string logon as a user with all these rights, which I really didn't want to do.
--Jim
--Jim
the ownership chain was same at sql 2000 too...
I'd swear at some point (long ago) I had an application in which I had to grant all users access to all tables that would be updated in sp's
Only if you were using Dynamic SQL.
Only if you were using Dynamic SQL.
No. Provided you are not using Dynamic SQL EXECUTE permissions allows you to SELECT, INSERT, UPDATE and DELETE.