1

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?

2
  • 1
    So what is your question?
    – Dale K
    Dec 19, 2023 at 8:40
  • I'm not sure where did you get the processadmin definition. When I look at the docs (even as far back as 2016), it says Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server. Which basically means KILL.
    – Roger Wolf
    Dec 19, 2023 at 8:44

1 Answer 1

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...

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.