If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects.
Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.
This is useful because: I will describe two basic templates that can be used for many commonly-seen forms and web pages, which can then be elaborated as required.
Occasionally in this article I will refer to an application that my company recently and successfully developed using this approach, along with the reasons why it was successful.
When faced with a complex business application that had to be delivered with minimum staffing, on-time and within budget, Dwain's team chose to encapsulate and implement most of an application's business logic in SQL Server, using an interface made up of stored procedures.
Without this approach, the team was convinced that it would not have been possible to deliver that level of business logic complexity within the timeframe.
Dwain explains how it was done, and provides a template.
For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables.You can invoke a stored procedure in different ways: You can invoke using exec or execute and even you can invoke the stored procedure without the execute statement.You do not necessarily need to specify the schema name. You need to specify the schema to invoke it (which is a good practice to avoid conflicts with other object with the same name and different schema).Let’s call a function without the schema: Celsius is the input parameter and we are doing the calculations in the select statement to convert to Fahrenheit degrees.If we invoke the stored procedure, we will verify the result converting 0 °C: As you can see, you can easily concatenate a function with a string.
We will show some Table-Valued Functions in the future. We will include the following topics: If you compare the code, the function requires more code to do the same thing.