No Application Logins Allowed - SOX

Hi there,
I'm trying to work through an issue regarding our MS SQL Server logins.
Our development folks are currently using application logins. That is, the
"Customer" application uses a login to SQL Server called "Customer" and the
appropriate rights are granted to the Customer login. We've recently been
informed that application logins do not offer the required security
(auditing) needed by the industry. From what I've been able to find we've
got a couple options:
1. Grant our users Windows Authentication access to SQL Server.
2. Use Application Roles.
There seem to be an issue surrounding both.
First, we certainly don't want every user granted direct access to our SQL
Servers. If anyone got a hold of an interactive SQL client (Query Analyzer
and MS Access come to mind) we'd be screwed. Although we could audit the
fact that someone screwed our database, it doesn't seem like that type of
thing would be in the best interest of me keeping my job.
Second, the use of application roles sound good but I'm told that having our
developers call sp_SetAppRole before any Select, Exec, Update, Delete or
Insert would kill their performance. We are running using disconnected type
d
datasets which allow the developers to disconnect after retrieving data.
I'm not sure what options are left. On one hand we want our users to be
granted access to SQL server so that thier own login can be tracked and
audited. On the other I don't want to allow every user to have direct
windows authenticated access to the databases.
Any insights would be appreciated.
Thank you.On Tue, 21 Jun 2005 07:12:13 -0700, Mike Q wrote:

> Any insights would be appreciated.
> Thank you.
a) Individual users don't have to be added to databases; Windows security
groups will do. As usual, a user receives access equal to the highest
access permitted by any matching group, unless some group denies him that
access.
b) Require developers to do all retrieves AND updates through stored
procedures. A stored procedure can limit the number of rows returned or
affected; it can check APP_NAME() to make sure that it's not some random MS
Access or Query Analyzer; it can even enforce allowable hours if you're
paranoid enough. Then you can remove direct table/view access from the
database role.
This may also hit performance. But performance and security are tradeoffs;
get more of one, sacrifice the other.
Your other choice as I see it is to continue to use the "Customer"
application login, and use other means to apply relevant auditing of which
windows users make use of the login.