I am sure there are a number of different answers that people can/will provide. I have three suggestions.

The first is to consider using a userid that has no password. If the userid has no password, then you cannot log in using that userid. You can then grant that userid resource authority and and create stored procedures owned by that userid. When that stored procedure is executed, the procedure will execute "as the owning userid" but once the procedure exits, the effective user will revert back to the logged in userid. By encapsulating all the "resource" specific SQL in stored procedures, you have full control over what SQL gets executed with the higher privileges and let all the normal stuff happen as userids with lower privileges.

If you do not like the stored procedure approach (although that tends to be the most used and most preferred approach), then you might consider using the SetUser statement. Be aware though that in order for a userid to execute the SetUser statement, that userid must be granted DBA authority, so this second approach is really scary.

My third suggestion is to see if using Windows Integrated Login will get around the password problem completely. Note that in the case of Windows Integrated Login, your application will need to run on a Windows network and may need to impersonate a Windows User context; so there could be some high hurdles to jump over in that case.

Those three suggestions amount to my two cents worth, hopefully others will jump in and provide additional suggestions. Like I said though, the stored procedure approach is probably the more widely used (and safest) approach.

Karim

If you're worried about someone reverse engineering your application to get the username and password from it, there's not much you can do. From the server's point of view, a client is a client is a client - there's nothing that your application can do that Joe Hacker's application can't do if Joe Hacker has your username and password. So if we add the ability for your application to log in as a user and then "elevate" your login to have more permissions, there's nothing stopping Joe Hacker's application from doing the same thing.

We have something called the authenticated server, which only allows databases that have been authenticated to be run, and only allows applications which have been authenticated to connect. In order for an application to authenticate itself, it does a SET OPTION statement using a particular string, and the server validates the string. You may see the problem already - the string has to be stored somewhere that the application can read, and whether it's hard-coded in the application itself or stored in a file somewhere, a hacker with access to the application can get access to the string, and then his application is "authenticated".

The most secure way to get around this is simply not to store the password in your application, though this is admittedly inconvenient.