0

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.

Many thanks.

Chris Cannon
  • 103

1 Answers1

1

It sounds like you can make the user the database owner since you didn't list very strict security rules. I do this for all of my simple sites that need basic database access. If you need a more stringent policy (i.e. some database users are read-only, some can only access certain tables) then this is not the option for you.

In SQL Server Management Studio, expand Security > Logins and right click the user and go to Properties, then User Mapping. In the top pane, check the box next to your database. Then find db_owner in the bottom pane and check that box. Now the user can perform all functions on this database including INSERT, SELECT, UPDATE, DELETE.

enter image description here

Database-Level Roles for more details on what each of the roles mean.

Jeff
  • 355
  • 2
  • 5
  • 13