Re: Exec permissions on Stored Procedure

Mark B (mark_bert@xxxxxxxxx) writes:
I'm still fuzzy on one thing if you could explain. Say you have a
situation where a stored procedure is performing an insert to a table
with a different owner and / or schema. Because of this the user (In
my case the aspnet machine account for a web app) must have insert
permission to the underlying table as well as execute permission to
the SP. With the SP, I can control what that account can insert, but
it seems if that account has insert permission to the table, if
someone got control of the account, would they not have a lot more
control over the table?

They would, and therefore you should be restrictive with granting direct
permissions on tables, particularly INSERT, DELETE and UPDATE and stick
to stored procedures. With the same owner as the tables.

Or use any of the new mechanisms in SQL Server 2005, which I describe
in my article.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Ability to "Send As" someone else.
    ... Wingnut typed: ... You do not have permission to send to this recipient. ... Try with another account. ... You might ask the owner if he really wants this - there's something ...
    (microsoft.public.exchange.setup)
  • Re: Ability to "Send As" someone else.
    ... You do not have permission to send to this recipient. ... Tried sending TO internal and external recipients from her outlook ... Try with another account. ... The owner of our company often wants his administrative assitant to ...
    (microsoft.public.exchange.setup)
  • Re: xp_msadenabled
    ... This has been seen before when the account that SQL Server is running under ... didn't have permission to access Active Directory. ...
    (microsoft.public.sqlserver.server)
  • Re: dts and access db
    ... How can I setup permission for sa account under \\server\data folder? ... The service account for SQL Server ... >>already opened exclusively by another user, or you need permission to view ...
    (microsoft.public.sqlserver.dts)
  • Re: EXECUTE PERMISSION DENIED on executing Sproc
    ... i don't have any privilege needed to change any permission on any of my ... > to the account you're using to work with your SQL server DB. ... > which in turn depends on which version of IIS you're using. ...
    (microsoft.public.dotnet.framework.aspnet)