Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Using the SQL Server APP_NAME function to control stored procedure execution

MSSQLTips author Siddharth Mehta By:   |   Read Comments (8)   |   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:

Open SSMS, and create a procedure in the database of your choice

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.

Open SSDT, and create a new report project

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.

Create a dataset using this connection

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
MSSQLTips author Siddharth Mehta
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
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.


Friday, June 14, 2013 - 7:51:28 AM - gprocopan 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

Use 

Application Name parameter

 

in connection string.

 

 


Wednesday, August 14, 2013 - 2:09:40 PM - Scott Coleman Read The Tip

We have web apps where users log in to the web site, and it creates connections with "Application Name=Webapp!ClientUserName".  The web app always connects with the same application login, so this lets us parse the actual username out of the APP_NAME() value.

One caveat is that playing with connection strings will affect connection pooling on the web server.



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



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.