SQL Server login with specific permissions

Question

I'm a junior DBA. I want to create a SQL Server login that can access all databases, take backups, read tables, and update table values.

I thought of giving that login processAdmin as server role and db_backupoperator as database role:

processadmin server role can manage the processes running in SQL Server and execute query

The members of db_backupoperator database role can back up the database

I'm expecting this login should only able to read and update tables via queries and take backups of databases before update.

I don't want this login to drop any table or delete any database or restart SQL Server itself or assign any roles to others.

Are these the proper roles to accomplish my stated goal?

Answer 1

You should never use process admin that can kill any query running of any user...

You should use a SQL user in all non-system db associates with the login and give it the db_datawriter, db_datareader and db_backupoperator database roles.

As an example, if you have 3 databases named DB1, DB2 and DB3 do :

CREATE LOGIN CNX_yamihero777 WITH PASSWORD = 'YaMiHeRo!777';
GO
USE DB1;
GO
CREATE USER USR_yamihero777 FOR LOGIN CNX_yamihero777
GO
ALTER ROLE db_datawriter ADD MEMBER USR_yamihero777 
GO
ALTER ROLE db_datareader ADD MEMBER USR_yamihero777 
GO
ALTER ROLE db_backupoperator ADD MEMBER USR_yamihero777 
GO

USE DB2;
GO
CREATE USER USR_yamihero777 FOR LOGIN CNX_yamihero777
GO
ALTER ROLE db_datawriter ADD MEMBER USR_yamihero777 
GO
ALTER ROLE db_datareader ADD MEMBER USR_yamihero777 
GO
ALTER ROLE db_backupoperator ADD MEMBER USR_yamihero777 
GO

USE DB3;
GO
CREATE USER USR_yamihero777 FOR LOGIN CNX_yamihero777
GO
ALTER ROLE db_datawriter ADD MEMBER USR_yamihero777 
GO
ALTER ROLE db_datareader ADD MEMBER USR_yamihero777 
GO
ALTER ROLE db_backupoperator ADD MEMBER USR_yamihero777 
GO

You will perhaps needs also the REFERENCES privilege...