Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
Windows Server

SQL Server 2008 R2 : Executing Stored Procedures

12/14/2012 3:25:22 PM

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.

Figure 1. Using the Execute Procedure dialog in SSMS.

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
Other -----------------
- SQL Server 2008 R2 : Advantages of Stored Procedures, Creating Stored Procedures
- Microsoft Dynamics CRM 4.0 : Silverlight - Deploying Silverlight Using IFrames, Notes Entity
- Microsoft Dynamics CRM 4.0 : Silverlight - Developing a Basic Silverlight Application
- Windows Server 2008 Server Core : Outputting Data Files with the Type Command
- Windows Server 2008 Server Core : Replacing Existing Files with the Replace Utility, Taking Ownership of Files with the TakeOwn Utility
- Microsoft Dynamic GP 2010 : Tools for Dynamics GP
- Microsoft Dynamic GP 2010 : Purchase Order Processing
- Windows Server 2003 : Protecting Hosts with Windows Host Firewalls - Internet Connection Sharing
- Windows Server 2003 : Protecting Hosts with Windows Host Firewalls - Firewall Basics
- Collaborating Within an Exchange Environment Using Microsoft Office SharePoint Server 2007 : Exploring Basic MOSS Features
 
 
Top 10 video Game
-   Renoir [PS4/XOne/PC] Kickstarter Trailer
-   Poly Bridge [PC] Early Access Trailer
-   Renoir [PS4/XOne/PC] Gameplay Explanation Trailer
-   Renoir [PS4/XOne/PC] More About Trailer
-   King's Quest: A Knight to Remember [PS3/PS4/X360/XOne/PC] Complete Collection Trailer
-   Samurai Warriors Chronicles 3 | Announcement Trailer
-   FIFA 16 | No Touch Dribbling with Lionel Messi
-   Why We're Cautiously Optimistic For The Final Fantasy VII Remake
-   Civilization: Beyond Earth – Rising Tide [PC] E3 Gameplay Walkthrough
-   Why We're Excited For the FFVII Remake
-   Mortal Kombat X | Predator Brutality
-   Mortal Kombat X | Predator Fatality
-   Poly Bridge [PC] Early Access Trailer
-   Silence: The Whispered World 2 [PS4/XOne/PC] Cinematic Trailer
-   Devilian [PC] Debut Trailer
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
programming4us programming4us
PS4 game trailer XBox One game trailer
WiiU game trailer 3ds game trailer
Trailer game
 
programming4us
Heroes Charge
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer