I am deploying an ASP.NET application and SQL Server (2008) database on a live (production) server. The physical server is running both SQL Server 2008 and IIS 7 - it is provided by a hosting company and is not part of our internal network.
I have a couple of questions regarding database security and the connection string for the ASP.NET application.
Previously I would create a database user and specify the SELECT/INSERT etc. permissions for each table - but my issue is that there are 50+ tables in this database, so doing this would take a long time.
The application requires SELECT/INSERT/DELETE/UPDATE on each table.
- Is there a better way than specifying the permissions for each table individually?
- Is there an equivalent of integrated security for a live web server - what are the drawbacks?
- Or is there a way of elevating the access rights for a particular user to full access for a particular database
Also how would the connection string change?
I just looking for some expert advice, just someone to point me in the right direction and a link to some documentation on how to achieve a better way of doing it.