SQL Server Stored Procedures and Permissions

Question

Is it possible to deny permissions for everything in SQL Server (for example, select, insert, etc.) and provide temporary permissions through stored procedures. I want to use only stored procedures to access data in sql database.

Is this possible or is there another way?

Answer 1

This is a good template that supports my SQL Server.

Basically, anyone who has permissions on stored procedures does not need any permissions for tables at all if proc and tables have the same owner (usually dbo). It was called a chain of property rights.

Note. Table permissions are simply not checked in this situation, so explicit "DENY permissions" will also be ignored (DENY is different from "no permissions")

The same applies to views, functions, etc. (e.g. viewing a call table, viewing a proc call) ...

Answer 2

Yes, this is called ownership chaining , if you give permission to execute proc, the user will be able to execute proc, and he will work, he will receive data back

if he then tries to make a selection from a table from SSMS, it will not work (provided that he is not in a data reader or a higher role)

Keep in mind that dynamic SQL is broken down into ownership chaining , if you have dynamic SQL in a stored procedure, you will need additional permissions

Answer 3

This not only works with stored procedures, but also with views.
In general, you can give the user permission to an "higher level" object, such as view or sproc, without having to provide the same user permission for the base table (s).