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.
Best Answer
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.
EDIT:
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.