How many times have you provided someone permission and has just overlooked Application Roles. Rest aside the developers but even many DBAs have never used it. I was also one of those who has never seen anyone using Application Role.
Sometime back I got the request to change the password for an Application role in SQL Server I was confused and for a second I thought that is something not related to SQL Server. 🙂
What is an Application Role?
An application role is a database principal that enables an application to run with its own, user-like permissions. –docs
From the docs it could be a bit misleading when it says enables an “application”, as it is a database principal which can be used by anyone having the password to “activate” it.
Example, you can create logins to allow users connect to the SQL Server and provide them permission to run queries on a onto the database without directly providing them the permission on the database, and this can be done using Application Role.
How the Application Roles work?
Using Application roles involves three step,
1. Creating the Application role
2. From the application activating the role
3. When the work is done than deactivating the role.
1. Creating the Application Role.
This is pretty straight forward, expand the security section for the database go to role and Application Roles, right click and select ‘New Application Role’
In the New Application Role window, provide a name, a strong password and the permission. For permission you can either select the database roles or provide any permission using Securables. I have provided the Select, Insert and Update permission to the Application role named “TestingRole”.
2. Once you have got your role created you need to enable the logic in the application to activate it.
The below code has to be added into the application logic when making any modification. (Replace TestingRole with your Application Role name and MyStr0ngP@$$w0rd by the password for your App role)
DECLARE @cookie varbinary(8000); EXEC sp_setapprole ‘TestingRole’,’MyStr0ngP@$$w0rd’, @fCreateCookie = true, @cookie = @cookie OUTPUT;
3. When you no longer need the elevated permissions you have to switch back to your old permissions, and for doing this use the below query.
EXEC sp_unsetapprole @cookie;
The @cookie created when setting up the application role is used when rolling back the permissions. When using T-SQL you need to have, activation of App Role, using the elevated permissions, and reverting back, in the same T-SQL batch. Like below:
DECLARE @cookie varbinary(8000); EXEC sp_setapprole 'TestingRole','MyStr0ngP@$$w0rd', @fCreateCookie = true, @cookie = @cookie OUTPUT; UPDATE DimEmployee SET FirstName = 'Goy' WHERE FirstName = 'Guy' EXEC sp_unsetapprole @cookie;
To change the password, the task that I had in hand.
Go to the Application Role – Open it and enter the new password.
I hope it helps!