Using the SQL Server APP_NAME function to control stored procedure execution

By: Siddharth Mehta   |   Read Comments (6)   |   Related Tips: More > Functions - System

Problem

Logic reusability is one of the most practiced aspects of database development. For example query / business logic developed in stored procedures for one application (say a .NET page for example), can be easily reused by another application (a SSIS ETL package or for a SSRS report for example). Many times, this is not the intention. Provided that the application can make a connection and has the required privileges to connect to the database and use the database objects, one needs to still have a level of control over the database object to check the scope of the application and facilitate logic execution based on that specific need.

In this tip we will look at one way to achieve control of stored procedures to ensure that reuse is for the intended purpose and changes do not break other applications that may be using this same code.

Solution

Typically in a solution development life-cycle, an application starts with front-end development with a back-end database. A database would contain database objects to host as well as query data.

Generally a standard practice is that an application ID is created at a solution level. This ID is a Windows ID and meant to be used by all the front-end components of a solution to connect to the database using Windows integrated security and fetch the necessary data. Users would connect to the application using their own credentials and to facilitate data based on the role of the user, the application would connect to the database using the application ID.

For example, if the solution has components like a front-end, web services, ETL packages, reports etc..., then all would be connecting to the database using the same application ID. Now consider the scenario that a typical stored procedure was created to be used only by web services. Other teams can see this SP and intend on using this SP for their component. So how do we make sure that even if a database user has privileges on the stored procedure it should execute only for the application that it's targeted for?

Using Application Name

One of the easiest solutions in this case is by setting the "Application Name" property in the connection string and verifying this name in the SP using the "App_Name" SQL Server system function. To test this scenario, follow the steps below.

Step 1

Open SSMS and create a stored procedure in the database of your choice as shown in the below screenshot. In my case I have created this stored procedure in the AdventureWorks database. This procedure will check the application name returned by the connection and return the name of the application.

Execute this stored procedure from SSMS and check the result:

Step 2

Open SSDT and create a new report project. Add a new report to the project and create a new connection.

If you browse to the Advanced settings of the connecting string dialog box you will find a connecting string parameter named "Application Name". Set the value of this property to "App SSRS Reports" and click OK.

By setting this property value, a parameter named "Application Name" will be added to the connection string as shown in the below screenshot.

Step 3

Create a dataset using this connection and use this on the report. Execute the report and you should get something similar to the below screenshot.

Using the APP_NAME function it is possible to add checks into SPs whether the call is made by the intended application and depending upon the application name the appropriate decision can be made whether to execute the logic for the execution request. Your stored procedure can easily be modified to check the application name and if it is the intended application the rest of the code in the stored procedure executes if not the procedure would skip the logic and just return.

This function can be also useful for logging multiple applications that share the same SP. You can create a table to collect this data and then use this data to analyze how this SP is being used by all of the applications that utilize this stored procedure. 

Next Steps

• Create different versions of the same report and use the version of the report in the application name parameter of the connection string.
• In the SP that provides data to the report, use the APP_NAME function to allow only selected versions of the report to execute the query logic inside the stored procedure.

Last Update: 3/11/2013

About the author

Siddharth is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips

We Recommend

• More Database Developer Tips...

Comments and Feedback:

Monday, March 11, 2013 - 12:02:06 PM - eric81	Read The Tip
So by defining in the Application Name 'App SSRS Reports' in your connection properties that's what will return when your run the App_name function?  Does that function provide you with more options then say sys.dm_exec_requests output?

Monday, March 11, 2013 - 7:17:31 PM - DVP Rao	Read The Tip
What is the default value if a Developer does not bother to fill in this field  ? Would you say this would be a best practice all coding to populate this property ?

Tuesday, March 12, 2013 - 12:55:05 PM - eric81	Read The Tip
Non of our developers use this feature and I referenced the wrong table in my last post.  if I define application name say as 'MY TEST' when i go to sys.dm_exec_sessions is that will show up under the Program_Name column?

Tuesday, March 12, 2013 - 1:06:48 PM - eric81	Read The Tip
DVP Rao, I answered my last post no need to respond.  thanks.

Friday, March 29, 2013 - 8:18:21 AM - SD	Read The Tip
Hi, I am getting "This Process was started by .Net SqlClient Data Provider" instead of ApplicationName. Can anyone help me how to get the asp.net application name

Monday, April 01, 2013 - 9:56:51 AM - Steven howes	Read The Tip
Pretty Cool, I'm now putting this in all my reports.  It's going to make trouble shooting problem reports a breeze if I can see the report name in SSMS.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

SQL Server having some performance issues? Idera SQL check. FREE SQL Server enhancement. SQL Monitor: prioritize your SQL Server workload with easy-to-use performance monitoring Need SQL Server help and not sure where to turn? Reach out to expert consultants in the USA for a Health Check. Join the over million SQL Server Professionals who get their issues resolved daily. The SQL Server Security THREAT - It’s Closer Than You Think