1

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.

Improve this question
2
  • You want the db_datareader and db_datawriter roles, which are set at a database level, not a server level.
    – Randolph West
    Commented Nov 14, 2016 at 21:08
  • Can you associate those roles at a server level?
    – Zach Smith
    Commented Nov 14, 2016 at 21:10

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • After creating a role called 'superadmin', with all securables enabled and as with membership to all server roles i find that though I can connect to a DB and run SELECT queries on tables, the result sets are empty. Do you know why this is the case? Also, is there a reason to be careful when adding allowing all securables on a server role? (other than the risk of users of that role having access to all parts of the database)
    – Zach Smith
    Commented Nov 14, 2016 at 21:31
  • It should be enough to give the user db_datareader and db_datawriter in the database. If you gave the user all roles in the database they are also dbo (and can drop the database) and db_denydatareader and db_denydatawriter and cannot access any data.
    – Spörri
    Commented Nov 15, 2016 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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