Application Roles in SQL Server

Posted on January 5th, 2008 | by Admin |

Application roles are set of permissions that you can group together for a specific application. When a user connects to the database through the specific application, he or she will be able to perform the actions permitted under the specific application role.

I have not used application roles in any of my applications yet. I am not sure if I will ever use it in any of my applications. But a little bit of reading on application roles will be good. I recently found a good introductory article about application roles at www.sqlservercentral.com. You can find the article here.

How to find the application role that is currently active?

I recently found this question in one of the MSDN forums and did some search to find an ansser. I found that when an application role is activated, USER_NAME() will return the name of the application role. Here is a piece of code which demonstrates this. Part of this example is taken from this MSDN article.

– create an app role

EXEC sp_addapprole ‘JacobsApplication’, ‘jacob$$’

GO

DECLARE @cookie varbinary(8000);

EXEC sp_setapprole ‘JacobsApplication’, ‘jacob$$’

, @fCreateCookie = true, @cookie = @cookie OUTPUT;

– The application role is now active.

SELECT USER_NAME();

– This will return the name of the application role, JacobsApplication.

EXEC sp_unsetapprole @cookie;

– The application role is no longer active.

– The original context has now been restored.

GO

SELECT USER_NAME();

– This will return the name of the original user.

GO

Sorry, comments for this entry are closed at this time.