I have been researching the use of Application Roles within Microsoft SQL Server and was wondering if there is a way to limit a user to only be able to set specific application roles. As I understand, any user of the database with sys.sp_setapprole execute permissions can set any application role they choose, as long as they provide the application role name and password. To me this seems like a trade-off in security.

For example, let's say John works in Accounting and uses an application named Accounting.exe, which is hard-coded to set the Accounting_appRole once connected to the AdventureWorks Database. Bill works in Finance and uses an application named Finance.exe, which is hard-coded to set the Finance_appRole once connected to the AdventureWorks Database. Both John and Bill must, at a minimum, have execute permissions for the sys.sp_setapprole stored procedure of the AdventureWorks Database. Therefore, if John from accounting e-mails the Accounting.exe application to Bill in finance, Bill can execute the Accounting.exe application without issue, which is undesired.

I think there should be a way to limit who can set a specific application role, does anyone know of a way? All I can come up with is modifying the sp_setapprole stored procedure to use SUSER_SNAME() to limit the value(s) of @rolename that can be used.

  • 1
    This is off topic and belongs on dba.se – Kermit Apr 9 '13 at 15:35
  • Sorry, @FreshPrinceOfSO I requested that it be moved. – CanMan Apr 9 '13 at 18:56

I wouldn't recommend messing with the system stored procs. You could wrap the sp_setapprole call in a stored proc and have your app call that proc instead. The wrapper proc would have the password hard-coded and use EXECUTE AS to impersonate a user who can use sp_setapprole. Then you could limit access to the wrapper proc. Another advantage to this approach is that the password doesn't go out across the network, nor is it included in your .exe file.


My mistake, you can't wrap the call directly. But you can wrap a modified version of the T-SQL that it runs. Looking at the version in my 2008r2 server, I see that the proc calls SETUSER intead of EXECUTE AS. Sad to see deprecated code in system stored procs. For yours, you may want to use EXECUTE AS instead.

You could also grant rights on whatever your app needs to function to a SQL user who can't login, give certain users rights to impersonate that user, and have your app EXECUTE AS the login-less user. This route still requires cookie handling to set the context back. In fact, it's very similar to a customized version of sp_setapprole, since both require some sort of impersonation.

Or, you could also create a database role and grant the role to authorized users. This seems like it might be more like what you want. Application roles are designed for app-defined rights. It seems like you really want user-defined rights.

  • I agree, modifying the sys sproc is not a good idea. Not sure about the wrapper yet, seems like yet another layer of complexity. Especially trying to handle the cookie for unsetapprole in code. – CanMan Apr 9 '13 at 19:17
  • Just tried calling the sp_setapprole from another stored procedure, received the Error: Application Roles Can Only Be Activated at the Ad Hoc Level. This is because the sp_setapprole does a if (@@nestlevel > 1) check. See: support.microsoft.com/kb/310824 – CanMan Apr 9 '13 at 20:14
  • The EXECUTE AS with a login-less user seems to be a sufficient solution since you can grant impersonate of that user to specific users. I am trying to limit/hinder a users privileges in a way that requires them to access the database through my app and only my app, or makes it very hard to do it any other way, while still using Windows Authentication. – CanMan Apr 10 '13 at 4:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.