SQL Server Security – Purpose of SQL Server Application Roles

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.

Please stop using the user instance=true setting. Not only has this feature been deprecated, but what is actually happening here is that Management Studio is firing up a different user instance of SQL Server than the one your application is using, so it is not surprising that you can't see the same thing in both cases.

(Usually, the symptom of this is that someone runs an update from their app, then checks the table in Management Studio, thinking there is a bug but not realizing that the update affected a different copy of the database.)

Create the database on your running SQL Server instance (or attach it there). Then connect to that server the same way, without the User Instance setting, from both SSMS and your app. Lots more information here. Don't create the database every time the application starts - that just sounds horrendously wrong.