Database Permission Help

Question

We just moved over to a new SQL 2005 server and are hosting the ConfigurationManagement, EOC and NetPerfMon databases on it.  In addition to the Read-Write accounts, we also require Read-Only access accounts.  I'm seeing a lot of accounts that need to be removed and I'd like to standardize my permission sets based on whether the account is Read-Only (RO) or Read-Write(RW).  I want to ensure that I'm giving these accounts the minimum permissions required to access the database in a RW or RO mode.  Since I don't have the SA password, I want to ensure that my RW account has enough permissions to complete all required Configuration Wizard tasks for database creation and updates.

For my SolarWindsNPM account which will have RW access to all three databases, I'm planning to map that user to the following server Roles: dbcreator, public, securityadmin and sysadmin.  Do I need sysadmin mapped?  In the user mapping section the user will be mapped to all 3 databases with a default schema of dbo and each database will have db_owner selected as the role for membership.

For my read-only account which will access two databases, I'm planning to map that user to the following server Role: public.  In the user mapping section the user is currently mapped to dbo for the Default Schema but I'm wondering if I can go ahead and change it to db_datareader since it is a read-only account?  Each database will have db_datareader and public selected as the role for membership.

I have not completely implemented these recommendations and I wanted to get some feedback from the community on this before I do.  Is there anything I'm missing here? I certainly don't want to break anything, I just want to establish a standard template that can be used at all of our sites.

Thanks for the help and Happy Thanksgiving!

Robert

bshopp · Answer

I am not sure, you could try to leave it on during setup and then turn it off afterwards.

rsprim · Answer

Hey Brandon,

Thanks for checking.  Is there any reason I need to have the SysAdmin Role checked in a RW situation?  As for the RO accounts, you are correct.  They will only be used to query the database.

Robert

bshopp · Answer

I checked with Dev and your read/write setup sounds adequate, though I don’t think we’ve formally tested it.

You don’t specify what you intend read-only users to be able to do. If you just wants those users to be able to query the database, that’s fine. If you intend to run the website with a read-only account, that’s not going to work. The website writes to (just off the top of my head) Accounts and WebUserSettings during normal operation.