Converting To Windows Authentication? Suggestions, Please

I am presently using MS SQL Server 2000; I have an authentication problem for which I do not have a good solution. Here are the main issues related to the problem:


Standards mandate we change to use Windows authentication
We have an old fat client application that presently works using SQL authentication
We have ad hoc and report users that are members of a domain group that has read only privileges in the database
The database is a union of 8 distinct databases that all run on the same server
There are nearly 1000 stored procedure with a couple hundred of them using references to one of the other 8 databases €“ a couple hundred cross database procedures
All update and record maintenance is performed within the context of the stored procedures; presently, only the login used by the application can execute these procedures
Presently, we can segregate the ad hoc users from the stored procedures because the ad hoc users have a login that is distinct from the application login
Application roles are not usable because of all of the cross database queries and stored procedures; therefore, database roles are used instead of application roles

The problem that I see with trying to go to windows authentication here is that the moment I go to windows authentication I lose the ability to segregate the application stored procedures from the ad hoc users. It seems to me that when we switch to Windows authentication that there is nothing to prevent the ad hoc users from using windows authentication instead of SQL authentication to do any ad hoc work. If this is the case, I see nothing to prevent them from using the stored procedures. Yes, I could grant the users seperate SQL authentication logins, but this still doesn't prevent them from authenticating using windows authentication.

It appears to me that in this case that the moment I change the way that the application authenticates to the database to windows authentication that the ad hoc users get an €œauto upgrade€? to update permission with their ad hoc logins. It would be great if I could keep the users with their read-only group privileges and use application roles to augment their privileges through the application; however, the application roles really only work for a specific database and all of these cross-database stored procedures cause me a big headache with respect to application roles.

To me it looks like modifying the application to use windows authentication is going to create a big security hole that did not previously exist. Does it look best in this situation to stay with SQL authentication? Is there a way out of this problem? I need suggestions.


Somewhat baffled,
Dave