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.

1 Answer 1

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.

9
  • Why db_owner and not db_writer? Apr 16, 2012 at 18:48
  • I dont believe db_datawriter can read data. db_owner can also do other things like EXECUTE stored procedures.
    – Jeff
    Apr 16, 2012 at 18:59
  • Ok but will db_owner DEFINITELY give the database user SELECT/INSERT/UPDATE/DELETE/EXECUTE on all tables / stored procs? What about UDFs? Apr 16, 2012 at 19:03
  • If db_owner is too loose for you, you can probably get away with assigning the user to db_datareader and db_datawriter to cover both bases.
    – Jeff
    Apr 16, 2012 at 19:03
  • Yes, according to MSDN, Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database.. I added a link in my answer for you to refer to.
    – Jeff
    Apr 16, 2012 at 19:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .