Using the SQL Server APP_NAME function to control stored procedure execution

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:

Step 2
Open SQL Server Data Tools 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.

http://www.mssqltips.com/sqlservertip/2897/using-the-sql-server-appname-function-to-control-stored-procedure-execution

What version of Office am I using?

You can use the screen captures and steps below to see which version of Microsoft Office is installed on your computer.

Office 2003

Office 2003 has text-based menus on the gray bar: File, Edit, View, Insert, Format, Tools, Table, Window, and Help. The following shows Word 2003.

1. Click Help > About.
2. In the About dialog box, find the version and the Service Pack (SP). Product licensing appears in the This product is licensed to field.

Office 2007
Office 2007 introduced tabs and the Microsoft Office Button and a tab menu. The following shows Word 2007.

1. Click the Microsoft Office Button > Word Options.
2. In the Options dialog box, click Resources. Find the version and the Service Pack (SP).

Note To learn about licensing, in Resources, click About. On the About dialog box, see This product is licensed to.
 

Office 2010

Office 2010 introduces the File tab and the Microsoft Office Backstage view. The following shows Word 2010.

1. Click File > Help.
2. Under Product Activated, find the version.

Note To learn about licensing, see About Microsoft Word.
Office 2013
The latest version of Office still uses the File tab and the Microsoft Office Backstage view. The following shows Word 2013.

1. Click File > Account.
2. Under Product Information, find the version.

Note To learn about licensing, click About Word.
 

Office 2013 RT

Office 2013 RT also uses the File tab and the Microsoft Office Backstage view. The following shows Word RT.

1. Tap File > Account.
2. Under Product Information, find the version.

Note To learn about licensing, tap About Word.