Using the SQL Server APP_NAME function to control stored procedure execution

By:   |   Comments (9)   |   Related: > Functions System


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.


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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, March 6, 2015 - 5:43:51 AM - Thomas Franz Back To Top (36458)

Nice idea but it could be have a drawback named parameter sniffing (see for an example / explanation), so you should test carefully (particullary users that queries f.e. ACL or config tables with the app_name (or username in Scott Coleman's example)).

Wednesday, August 14, 2013 - 2:09:40 PM - Scott Coleman Back To Top (26308)

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.

Friday, June 14, 2013 - 7:51:28 AM - gprocopan Back To Top (25436)


I am getting "This Process was started by .Net SqlClient Data Provider" instead of ApplicationName.

Can anyone help me how to get the application name


Application Name parameter


in connection string.



Monday, April 1, 2013 - 9:56:51 AM - Steven howes Back To Top (23098)

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, March 29, 2013 - 8:18:21 AM - SD Back To Top (23071)


I am getting "This Process was started by .Net SqlClient Data Provider" instead of ApplicationName.

Can anyone help me how to get the application name

Tuesday, March 12, 2013 - 1:06:48 PM - eric81 Back To Top (22753)

DVP Rao,

I answered my last post no need to respond.  thanks.

Tuesday, March 12, 2013 - 12:55:05 PM - eric81 Back To Top (22750)

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?

Monday, March 11, 2013 - 7:17:31 PM - DVP Rao Back To Top (22716)

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 ?


Monday, March 11, 2013 - 12:02:06 PM - eric81 Back To Top (22707)

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?

get free sql tips
agree to terms