In SQL Server, you can add a server role and give that role membership to other roles such as:
- bulkadmin
- dbcreator
- diskadmin
- processadmin
- securityadmin
- serveradmin
- setupadmin
- superadmin
However none of these roles seem to allow for only interacting with databases (SELECT, INSERT, UPDATE).
What securables should I enable to specifically allow for this kind of access? I would not want this server role to be able to create and drop databases.
Best Answer
To give access to a database you need to use database roles and database users but not server roles. Please check difference between a user and a login
You can create a server role, add logins to that. Then you can add that role as a user in each database and add it as a member of a database role that has select, insert and update rights.
If you do this in the model database and then that would be copied to all user databases created after that.