1
0
Procedure Execution Right: A caller’s rights stored procedure runs with the privileges of the caller. The primary advantage of a caller’s rights stored procedure is that it can access information about that caller or about the caller’s current session. For example, a caller’s rights stored procedure can read the caller’s session variables and use them in a query.
An owner’s rights stored procedure runs mostly with the privileges of the stored procedure’s owner.
At the time that the stored procedure is created, the creator specifies whether the procedure runs with owner’s rights or caller’s rights. The default procedure Execution Right is owner’s rights.
We have created a procedure with execution rights as “EXECUTE AS CALLER” through SYSADMIN role.
Now grant this procedure to the REP1 role assigned to the new user.
Now Login to the User having Role REP1 assigned.
As we can see even new user have the rights to execute the Procedure but he is not authorize to view the table and hence during the execution of procedure, Caller privileges came into the relevance and not able to view the output.
Now recreate or ALTER the Procedure with EXECUTE as OWNER and execute it via new User
Login with the new User and execute the procedure:
Privileges on Database Objects:
A caller’s rights stored procedure runs with the database privileges of the role that called the stored procedure. Any statement that the caller could not execute outside the stored procedure cannot be execute inside the stored procedure, either.
An owner’s rights procedure runs with the rights of the procedure owner. This means that if the owner has the privileges to perform a task, then the stored procedure can perform that task even when called by a role that does not have privileges to perform that task directly
However, to know more how to SET a variable inside a stored procedure. Click here.
