To execute a stored
procedure, you simply invoke it by using its name (the same way you
probably have already executed system stored procedures, such as sp_help).
If the execution of the stored procedure isn’t the first statement in a
batch, you need to precede the procedure name with the EXEC keyword. Following is the basic syntax for executing stored procedures:
[EXEC[UTE]] [@status =] [schema].procedure_name[; number]
[[@param_name =] expression [output][, ... ]]
[WITH RECOMPILE]
Note
The reason you need the EXEC keyword when
invoking a stored procedure in a batch or other stored procedure is
quite simple. SQL Server parses the commands sent to it in a batch by
searching for keywords. Stored procedure names aren’t keywords. If SQL
Server finds a procedure name among the SQL statements, chances are that
SQL Server will return an error message because it tries to treat it as
part of the preceding command. Sometimes the execution is successful,
but SQL Server doesn’t execute what you want. Consider this example:
SELECT * FROM titles
sp_help
The SELECT statement runs fine, but the procedure is not executed. The reason is that sp_help ends up being used as a table alias for the titles table in the SELECT statement.
However, if you precede the procedure name with EXEC, like this, you get the expected behavior:
SELECT * FROM titles
EXEC sp_help
Why don’t you have to put EXEC
in front of the procedure name if the procedure is the first statement
in a batch? If SQL Server doesn’t recognize the first string in a batch,
it simply assumes that it is a name of a stored procedure. For example,
execute the following string and notice the error message:
Dsfdskgkghk
go
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'Dsfdskgkghk'.
As good programming practice, it is best to always precede stored procedures with the EXEC keyword. This way, it will always work as expected, whether or not it’s the first statement in a batch.
Executing Procedures in SSMS
To execute a stored procedure in SSMS, open the object tree for the database, open the Programmability folder, and open the Stored Procedures
folder. Then right-click the stored procedure, and from the context
menu, choose Execute Stored Procedure. SSMS then presents you with the
Execute Procedure dialog, as shown in Figure 1. In this window, you can enter values for any parameters contained in the stored procedure. If you want to pass a NULL value to a parameter, you need to be sure to place a check mark in the Pass Null Value check box for that parameter.
After you specify values for all the parameters, SSMS
opens a new query window with the generated execute statement and
automatically executes it. It displays any results in the Results
window. If the stored procedure contains output parameters, SSMS
generates local variables for the output parameters and uses a SELECT statement to display the values returned to the output parameters. Listing 1 shows an example of the execute script and its results for the procedure invoked in Figure 1
Listing 1. A Procedure Execution Script Generated by SSMS
USE [bigpubs2008]
GO
DECLARE @return_value int,
@p3 int
EXEC @return_value = [dbo].[myproc]
@p1 = 100,
@p2 = 200,
@p3 = @p3 OUTPUT
SELECT @p3 as N'@p3'
SELECT 'Return Value' = @return_value
GO
@p3
-----------
300
Return Value
------------
0
|
Execution Context and the EXECUTE AS Clause
Normally, stored procedures execute within the
security context of the current user. The user must have execute
permission on the procedure and if the objects referenced within the
stored procedure are not owned by the user who created the stored
procedure, the current user must also have the necessary permissions
granted on the referenced objects. The current user does not inherit the
permissions of the procedure creator. The only exception to this occurs when the objects referenced by a stored procedure are
owned by the same user who created the stored procedure. In this case,
permissions on the referenced objects in the stored procedure are
dependent on the ownership chain that exists between the calling
procedure and referenced objects. For example, if the creator of a
stored procedure also owns the table that it references, the user
executing the stored procedure inherits the rights on the referenced
table from the owner within the context of the stored procedure, without
having to be granted explicit rights on the table by the table owner.
However, there are limitations to using ownership chaining alone for inheriting access permissions:
The rights inherited by ownership chaining apply only to DML statements: SELECT, INSERT, UPDATE, and DELETE.
The owners of the calling and called objects must be the same.
The rights inherited by ownership chaining do not apply to dynamic queries inside the stored procedure.
In SQL Server 2008, you can implicitly define the
execution context of functions (except inline table-valued functions),
stored procedures, and triggers by specifying the EXECUTE AS clause. The EXECUTE AS
clause allows you to go beyond ownership chaining to specify the
security context under which a stored procedure will execute and what
access rights the user will have on the referenced objects. The EXECUTE AS
clause allows you to specify explicitly the security context under
which the stored procedure will execute. In other words, it allows you
to specify which user account SQL Server should use to validate
permissions on the database objects referenced by the stored procedure.
The user executing the stored procedure, in effect, impersonates the
user specified in the EXECUTE AS clause within the context of the execution of the stored procedure.
The EXECUTE AS clause can be specified when
the stored procedure is created to set the default security context for
all users when executing the stored procedure. Alternatively, the EXECUTE AS
clause can be specified explicitly within the stored procedure code or
within each individual user session. When specified in a user session,
the security context switches to that specified until the connection is
closed, a REVERT statement is run, or another EXECUTE AS statement is run.
The syntax of the EXECUTE AS clause for stored procedures is as follows:
{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }
You can specify the following security context options when using the EXECUTE AS clause:
CALLER—
This option specifies that the statements inside the stored procedure
are executed in the context of the caller of the module (that is, the
current user). The user executing the stored procedure must have execute
permission on the stored procedure and also permissions on any database
objects that are referenced by the stored procedure that are not owned
by the procedure creator. CALLER is the default behavior for all stored procedures, and it is the same as SQL Server 2000 behavior.
SELF— This option is equivalent to EXECUTE AS user_name, where the specified user is the person creating or modifying the stored procedure.
OWNER—
This option specifies that the statements inside the stored procedure
execute in the context of the current owner of the stored procedure. If
the procedure does not have a specified owner, the owner of the schema
in which the procedure was created is used. OWNER must map to a single user account and cannot be a role or group.
'user_name'— This option specifies that the statements inside the stored procedure execute in the context of the user_name
specified. Permissions for any objects within the stored procedure are
verified against this user. The user specified must exist in the current
database and cannot be a group, role, certificate, key, or built-in
account.
To determine the execution context of a stored procedure, you can query the execute_as_principal_id column in either the sys.sql_modules or sys.assembly_modules catalog view.
Specifying an execution context for a stored
procedure can be very useful when you want to define custom permission
sets. For example, some actions, such as TRUNCATE TABLE, cannot be explicitly granted to other users. However, if you use the EXECUTE AS
clause to set the execution context of a stored procedure to a user who
does have truncate table permissions (for example, a user who has
permissions to alter the table), you can then incorporate the TRUNCATE TABLE statement within the procedure. Any user to whom you then grant EXECUTE permission on the stored procedure is able to run it to execute the TRUNCATE TABLE command contained in it.
Tip
When using the EXECUTE AS clause to
customize the permission set for a stored procedure, it is good security
policy to specify a login or user that has the least privileges
required to perform the operations defined in the stored procedure. Do
not specify an account such as a database owner account unless those
permissions are required.
To specify the EXECUTE AS clause when you
create or modify a stored procedure and specify a user account other
than your own, you must have impersonate permissions on the specified
user account in addition to having permissions to create or alter the
stored procedure. When no execution context is specified or EXECUTE AS CALLER is specified, impersonate permissions are not required.
The following example demonstrates how the user context changes when you use the EXECUTE AS clause in the creation of a stored procedure:
use bigpubs2008
go
sp_addlogin fred, fred2008
go
sp_grantdbaccess fred
go
create proc test_execute_as
with EXECUTE AS 'fred'
as
select user_name() as 'User context within proc'
go
select user_name() as 'User context before EXEC'
exec test_execute_as
User context before EXEC
-------------------------------
dbo
User context within proc
-------------------------------
fred