chocoboryo
New Member
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.