Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 967
  • Last Modified:

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
0
prairie1
Asked:
prairie1
  • 2
  • 2
1 Solution
 
Anthony PerkinsCommented:
Logging back on as Test, I ran the proc and it ran successfully and updated the table.   Shouldn't this have failed?
No.  Provided you are not using Dynamic SQL EXECUTE permissions allows you to SELECT, INSERT, UPDATE and DELETE.
0
 
tigin44Commented:
sql server has an object ownership chain mechanism..  if you create a stored procedure and gave an execute permission to a user the user gets the required rights for the objects refered in the sp  belonging to the same schema as the sp. i.e... you created an sp like foo.spTest and granted the execute to the user johnDoe.. in you sp you are referencing to the tables foo.Table1 and dbo.Table2.. the user johnDoe do not hane any permissions on both tables. By the execute permission the user johnDoe gets the required permissions for the table foo.Table1 but not the table dbo.Table2. So to excute it the user also granted for the table dbo.table2 with required permissions explicitly. if all the object belong to the same schema the excute permission will be enough for the user to execute it successfuly.
0
 
prairie1Author Commented:
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
0
 
tigin44Commented:
the ownership chain was same at sql 2000 too...
0
 
Anthony PerkinsCommented:
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now