Database security is underrated topic. The majority of projects are using single user for all database calls. Doesn't matter if you have several machines accessing your database or users, everyone should access it with his own name. Besides of obvoius security aspect of the topic, there is also a practical one: you can easily profile/debug/log only relevant calls.
Just putting it here:
Assuming that you have administrative access to master database
- Create a new login for sql server:
CREATE LOGIN db_rwuser WITH password='pwd';
It would be a nice practice specifying permission in db users name, specially for machine users.
- Create user for specific database
CREATE USER db_rwuser FROM LOGIN db_rwuser;
permissionsdepending on your requirements
EXEC sp_addrolemember 'db_datareader', 'db_rwuser'; EXEC sp_addrolemember 'db_datawriter', 'db_rwuser';
Role definition according to Microsoft docs:
dbdatawriter Members of the dbdatawriter fixed database role can add, delete, or change data in all user tables.
dbdatareader Members of the dbdatareader fixed database role can read all data from all user tables.
Sponsor: Konso is set of Observability and Utility tools designed for helping development team to speed up delivery up to 30%. No agent installation is required. Create your free account now