Generates a report of the minimum system privileges required to run a stored procedure and pass the privilege check for the procedure.
sp_proc_priv ( [proc_name] )
You must have EXECUTE privilege on the system procedure.
| Column name | Data type | Description |
|---|---|---|
| proc_name | char(128) | The name of the stored procedure. |
| privilege | long varchar | The privileges required to pass privilege check. |
If multiple system privileges, separated by a comma, are displayed for a stored procedure, this implies that any one of them would suffice to execute the stored procedure. If multiple rows are displayed for a stored procedure, then one system privilege from each row is required to execute the stored procedure.
This procedure lists only those system privileges for a stored procedure that will always pass the privilege check for the procedure. There may be other system privileges which would pass the privilege check to execute the procedure given conditions, but these are not listed by this procedure.
If sp_proc_priv is invoked without any parameter specified, the procedure displays all the stored procedures and the system privileges required to execute each. Stored procedures which do not require any system privileges for their execution are not displayed.
If sp_proc_priv () is executed, output would be similar to the following:
| proc_name | privileges |
|---|---|
| sp_iqrowdensity | MONITOR, MANAGE ANY DBSPACE, CREATE ANY INDEX, ALTER ANY INDEX, CREATE ANY OBJECT, ALTER ANY OBJECT |
| sp_iqworkmon | MONITOR |
| sp_iqindexsize | MANAGE ANY DBSPACE, ALTER ANY INDEX, ALTER ANY OBJECT |
| sp_addlogin | MANAGE ANY USER |
| sp_iqemptyfile | BACKUP DATABASE, SERVER OPERATOR, ALTER DATABASE |
| sp_iqemptyfile | INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, ALTER ANY TABLE, LOAD ANY TABLE, TRUNCATE ANY TABLE, ALTER ANY OBJECT |
| ... | ... |
If sp_proc_priv is invoked with a procedure name parameter, it returns the system privileges required to execute that procedure. If no system privileges are required, it lists “No Privilege Required” against the procedure.
| proc_name | privileges |
|---|---|
| sp_iqindexsize | MANAGE ANY DBSPACE, ALTER ANY INDEX |
An error message appears if the procedure does not exist.