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
  • 3

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
  • 335
  • 2
  • 4
  • 13
  • 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
  • Ok thanks for your help, I'm going to try db_reader plus db_writer and then do the stored procs and functions manually as there is only 1 stored proc and 2 functions. Is there anything else I need to worry about? Will granting execute on a stored proc enable the app to work perfectly without looking inside to see what it is doing? – Chris Cannon Apr 16 '12 at 19:25
  • You will need `EXECUTE` and `SELECT` permissions to execute a sp that queries a table. SELECT will be accounted for by `db_datareader`. – Jeff Apr 16 '12 at 19:32
  • You should be able to inherit SELECT/INSERT/UPDATE/DELETE through procs; that is 1 of the reasons for procs. Since you only have 1 proc, it doesn't matter too much here. I would make the relevant user(s) db_datareader and db_datawriter. I would then create a new database role named "db_procexecutor", make my user(s) a member of that new role, then issue "GRANT EXECUTE TO db_procexecutor". That way, anyone who is a member of that role can execute any proc, UDF, etc. in that database, even if new ones are created later. It might be a little overkill here, but I think it's a good habit. – Darin Strait Apr 16 '12 at 20:39
  • What is the minimum permission for an program to connect to sql db and read the table using connection string (windows auth)? – variable Jul 28 '21 at 17:39