I have a C# project to automate Children Toys storehouse IN/OUT operations. My application uses ADO.NET to communicate with the SQL Server database. I have created a login named "LiBX". I deny all permissions on all my database table and grant
EXECUTE permissions on all stored procedures but when I run the application an error message appears:
The SELECT permission was denied on the object 'tableMatrials',database 'STORDB',schema 'dbo'
How can I deny all permissions in all database tables and allow SQL query to be executed from stored procedures only?
When you run a stored procedure, the queries execute in the context of the caller, but can be set to run as the Owner instead, giving a higher level of permission - use
EXECUTE AS OWNER. This can be a useful way to circumvent situations that aren't completely covered by ownership chaining.