I have a VB (VS2008) application which uses a standard db connection string to log into the SQL Server (2005 / 2008) database that it uses, with Windows Authentication mode:
Data Source=HostName\DBMS Instance; Initial Catalog=DatabaseName; Integrated Security=True
This works fine in development, but in the live environment it seems to require each specific user to be set up as a member of a security group within SQL Server in order to gain access to the database and perform the standard db table actions. This is obviously unwieldy as it may require several hundred users to be added, and then extensive maintence to keep the list up to date.
I am assuming that there is a better way of doing this, either by setting up a group with the appropriate permissions to which all members of the organisation belong ("Everyone"), or by creating an Application Role for the target database.
Can I get by with the first option (in which case how), or do I need the second approach, in which case what alteration do I need to make to my code to jump this new security hurdle? (Unfortunately, the SQL DBA doesn't seem to know very much and can't help.)
