Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
Why db_owner and not db_writer? –  Chris Cannon Apr 16 '12 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 '12 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? –  Chris Cannon Apr 16 '12 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 '12 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 '12 at 19:05
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.